Inhalte
Bei Datenbanken (DB) fallen einem natürlich zuerst allerlei Buzzwords wie SQL, Oracle, 1C oder zumindest Access ein. Natürlich sind dies sehr mächtige (und größtenteils teure) Programme, die die Arbeit eines großen und komplexen Unternehmens mit vielen Daten automatisieren können. Das Problem ist, dass manchmal eine solche Leistung einfach nicht benötigt wird. Ihr Unternehmen ist vielleicht klein und hat relativ einfache Geschäftsprozesse, aber Sie möchten es auch automatisieren. Und gerade für kleine Unternehmen ist dies oft eine Frage des Überlebens.
Lassen Sie uns zunächst die TOR formulieren. In den meisten Fällen sollte eine Datenbank für die Buchhaltung, zum Beispiel der klassische Vertrieb, in der Lage sein:
- halten in den Tabellen Informationen zu Waren (Preis), getätigten Transaktionen und Kunden und verknüpfen diese Tabellen miteinander
- bequem haben Eingabeformulare Daten (mit Dropdown-Listen usw.)
- einige Daten automatisch ausfüllen gedruckte Formulare (Zahlungen, Rechnungen etc.)
- das Notwendige herausgeben Berichte den gesamten Geschäftsprozess aus Sicht des Managers zu steuern
Microsoft Excel kann all dies mit ein wenig Aufwand bewältigen. Lassen Sie uns versuchen, dies umzusetzen.
Schritt 1. Ausgangsdaten in Form von Tabellen
Wir werden Informationen über Produkte, Verkäufe und Kunden in drei Tabellen speichern (auf demselben Blatt oder auf verschiedenen – es spielt keine Rolle). Es ist grundsätzlich wichtig, sie in „Smart Tables“ mit Auto-Size umzuwandeln, um in Zukunft nicht mehr daran zu denken. Dies geschieht mit dem Befehl Als Tabelle formatieren Tab Startseite (Home — Als Tabelle formatieren). Auf dem dann erscheinenden Reiter Bauherr (Design) Geben Sie den Tabellen beschreibende Namen im Feld Tabellenname zur späteren Verwendung:
Insgesamt sollten wir drei „intelligente Tabellen“ erhalten:
Bitte beachten Sie, dass die Tabellen zusätzliche klärende Angaben enthalten können. Also zum Beispiel unsere PREISLISTEenthält zusätzliche Informationen über die Kategorie (Produktgruppe, Verpackung, Gewicht usw.) jedes Produkts und die Tabelle Kunden — Stadt und Region (Adresse, TIN, Bankverbindung usw.) von jedem von ihnen.
Tisch Sales wird von uns später verwendet, um abgeschlossene Transaktionen darin einzugeben.
Schritt 2. Erstellen Sie ein Dateneingabeformular
Selbstverständlich können Sie Verkaufsdaten direkt in die grüne Tabelle eingeben SalesDies ist jedoch nicht immer bequem und führt aufgrund des „menschlichen Faktors“ zum Auftreten von Fehlern und Tippfehlern. Daher ist es besser, ein spezielles Formular zur Eingabe von Daten auf einem separaten Blatt wie folgt zu erstellen:
Verwenden Sie in Zelle B3 die Funktion, um das aktualisierte aktuelle Datum/Uhrzeit zu erhalten Die TDATA (JETZT). Wenn die Zeit nicht benötigt wird, dann stattdessen Die TDATA Funktion angewendet werden kann HEUTE bestellen (HEUTE).
Suchen Sie in Zelle B11 den Preis des ausgewählten Produkts in der dritten Spalte der intelligenten Tabelle PREISLISTE mit der Funktion VPR (SVERWEIS). Wenn Sie ihm noch nicht begegnet sind, dann lesen und sehen Sie sich zuerst das Video hier an.
In Zelle B7 benötigen wir eine Dropdown-Liste mit Produkten aus der Preisliste. Dazu können Sie den Befehl verwenden Daten – Datenvalidierung (Datenvalidierung), als Einschränkung angeben Liste (Liste) und dann in das Feld eingeben Quelle (Quelle) Link zur Spalte Name und Vorname von unserem smarten Tisch PREISLISTE:
Auf ähnliche Weise wird eine Dropdown-Liste mit Kunden erstellt, aber die Quelle wird enger sein:
=INDIREKT("Kunden[Kunde]")
Funktion INDIREKT (INDIREKT) wird in diesem Fall benötigt, da Excel leider keine direkten Links zu Smart Tables im Source-Feld versteht. Aber derselbe Link ist in eine Funktion „verpackt“. INDIREKT Gleichzeitig funktioniert es mit einem Paukenschlag (mehr dazu im Artikel zum Erstellen von Dropdown-Listen mit Inhalten).
Schritt 3. Hinzufügen eines Verkaufseingangsmakros
Nachdem Sie das Formular ausgefüllt haben, müssen Sie die darin eingegebenen Daten am Ende der Tabelle hinzufügen Sales. Mit einfachen Links bilden wir eine Zeile, die direkt unter dem Formular hinzugefügt wird:
Diese. Zelle A20 hat einen Link zu =B3, Zelle B20 hat einen Link zu =B7 und so weiter.
Lassen Sie uns nun ein zweizeiliges elementares Makro hinzufügen, das die generierte Zeichenfolge kopiert und der Sales-Tabelle hinzufügt. Drücken Sie dazu die Kombination Alt + F11 oder Taste Visual Basic Tab Entwickler (Entwickler). Wenn diese Registerkarte nicht sichtbar ist, aktivieren Sie sie zuerst in den Einstellungen Datei – Optionen – Menüband einrichten (Datei – Optionen – Menüband anpassen). Fügen Sie im sich öffnenden Visual Basic-Editor-Fenster über das Menü ein neues leeres Modul ein Einfügen – Modul und geben Sie dort unseren Makrocode ein:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Datenzeile aus dem Formular kopieren n = Worksheets("Sales").Range("A100000").End(xlUp) . Row 'Bestimmen Sie die Nummer der letzten Zeile in der Tabelle. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'Einfügen in die nächste leere Zeile Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'Ende des Unterformulars löschen Jetzt können wir unserem Formular eine Schaltfläche hinzufügen, um das erstellte Makro über die Dropdown-Liste auszuführen Insert Tab Entwickler (Entwickler – Einfügen – Schaltfläche):
Nachdem Sie es gezeichnet haben, halten Sie die linke Maustaste gedrückt, und Excel fragt Sie, welches Makro Sie ihm zuweisen müssen – wählen Sie unser Makro aus Hinzufügen_Verkaufen. Sie können den Text auf einer Schaltfläche ändern, indem Sie mit der rechten Maustaste darauf klicken und den Befehl auswählen Text ändern.
Jetzt können Sie nach dem Ausfüllen des Formulars einfach auf unsere Schaltfläche klicken, und die eingegebenen Daten werden automatisch in die Tabelle eingefügt Sales, und dann wird das Formular geleert, um ein neues Geschäft einzugeben.
Schritt 4 Tabellen verknüpfen
Bevor wir den Bericht erstellen, verknüpfen wir unsere Tabellen miteinander, damit wir später schnell den Umsatz nach Region, Kunde oder Kategorie berechnen können. In älteren Versionen von Excel würde dies die Verwendung mehrerer Funktionen erfordern. VPR (SVERWEIS) zum Ersetzen von Preisen, Kategorien, Kunden, Städten usw. in die Tabelle Sales. Dies erfordert Zeit und Mühe von uns und „frisst“ auch viele Excel-Ressourcen. Ab Excel 2013 lässt sich alles viel einfacher umsetzen, indem man Beziehungen zwischen Tabellen herstellt.
Dazu auf der Registerkarte Datum (Datum) klicken Sie auf Verhältnis (Beziehungen). Klicken Sie im angezeigten Fenster auf die Schaltfläche Erstellen (Neu) und wählen Sie aus den Dropdown-Listen die Tabellen und Spaltennamen aus, mit denen sie verknüpft werden sollen:
Ein wichtiger Punkt: Die Tabellen müssen in dieser Reihenfolge angegeben werden, also verknüpfte Tabelle (PREISLISTE) darf nicht in der Schlüsselspalte (Name und Vorname) doppelte Produkte, wie es in der Tabelle vorkommt Sales. Mit anderen Worten, die zugehörige Tabelle muss eine sein, in der Sie mit nach Daten suchen würden VPRwenn es verwendet wurde.
Natürlich ist die Tabelle auf ähnliche Weise verbunden Sales mit Tisch Kunden nach gemeinsamer Spalte Kundenfälle:
Nach dem Einrichten der Links kann das Fenster zum Verwalten von Links geschlossen werden; Sie müssen diesen Vorgang nicht wiederholen.
Schritt 5. Wir erstellen Berichte anhand der Zusammenfassung
Um nun die Verkäufe zu analysieren und die Dynamik des Prozesses zu verfolgen, erstellen wir zum Beispiel eine Art Bericht mit einer Pivot-Tabelle. Aktive Zelle auf Tabelle setzen Sales und wählen Sie die Registerkarte im Menüband aus Einfügen – PivotTable (Einfügen – Pivot-Tabelle). In dem sich öffnenden Fenster fragt uns Excel nach der Datenquelle (z. B. Tabelle Sales) und einen Ort zum Hochladen des Berichts (vorzugsweise auf einem neuen Blatt):
Der entscheidende Punkt ist, dass es notwendig ist, das Kontrollkästchen zu aktivieren Fügen Sie diese Daten dem Datenmodell hinzu (Daten zum Datenmodell hinzufügen) am unteren Rand des Fensters, damit Excel versteht, dass wir einen Bericht nicht nur auf der aktuellen Tabelle aufbauen wollen, sondern auch alle Beziehungen verwenden.
Nach dem Klicken auf OK In der rechten Hälfte des Fensters wird ein Panel angezeigt Pivot-Tabellenfelderwo Sie auf den Link klicken Alleum nicht nur die aktuelle zu sehen, sondern alle „intelligenten Tabellen“, die im Buch enthalten sind, auf einmal. Und dann können Sie, wie in der klassischen Pivot-Tabelle, einfach die Felder, die wir benötigen, aus beliebigen verwandten Tabellen in den Bereich ziehen Filter, Reihen, Stolbzow or Werte – und Excel erstellt sofort jeden Bericht, den wir auf dem Blatt benötigen:
Vergessen Sie nicht, dass die Pivot-Tabelle regelmäßig aktualisiert werden muss (wenn sich die Quelldaten ändern), indem Sie mit der rechten Maustaste darauf klicken und den Befehl auswählen Aktualisieren & Speichern (Aktualisierung), weil es das nicht automatisch machen kann.
Auch durch Auswählen einer beliebigen Zelle in der Zusammenfassung und Drücken der Schaltfläche Pivot-Diagramm (Pivot-Diagramm) Tab Analyse (Analyse) or Parameter (Optionen) Sie können die darin berechneten Ergebnisse schnell visualisieren.
Schritt 6. Füllen Sie die Ausdrucke aus
Eine weitere typische Aufgabe jeder Datenbank ist das automatische Ausfüllen von diversen Vordrucken und Formularen (Rechnungen, Rechnungen, Akten etc.). Ich habe bereits über eine der Möglichkeiten geschrieben, dies zu tun. Hier implementieren wir beispielsweise das Ausfüllen des Formulars nach Kontonummer:
Es wird davon ausgegangen, dass der Benutzer in Zelle C2 eine Zahl (Zeilennummer in der Tabelle Sales), und dann werden die Daten, die wir brauchen, mit der bereits bekannten Funktion abgerufen VPR (SVERWEIS) und Funktionen INDEX (INDEX).
- So verwenden Sie die SVERWEIS-Funktion zum Nachschlagen und Nachschlagen von Werten
- So ersetzen Sie SVERWEIS durch INDEX- und MATCH-Funktionen
- Automatisches Ausfüllen von Formularen und Formularen mit Daten aus der Tabelle
- Erstellen von Berichten mit PivotTables