Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen

Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen

Excel-Arbeitsmappen sind der nächste Schritt, nachdem Sie die einzelne Excel-Tabelle beherrscht haben. Gehen wir zurück zu der Ledger-Idee, die wir in unserem Excel-Tabellenkalkulationsleitfaden verwendet haben, und betrachten Sie die Arbeitsmappe als das Hauptbuch (oder Buch) und die Tabellenkalkulationen als die Seiten innerhalb dieses Buches.

Wir werden in diesem Handbuch tief in Excel-Arbeitsmappen eintauchen, mit den Grundlagen beginnen und im Laufe der Zeit mehr hinzufügen, also bookmarken Sie diese Seite und kommen Sie wieder, um mehr zu lernen.

Spreadsheet vs. Arbeitsbuch: Wie sie zusammenpassen

Tabellen und Arbeitsmappen sind eng miteinander verwandt. Wenn Sie Excel öffnen, zeigt die Software ein leeres Arbeitsblatt (Blatt1) an. Wenn Sie die Datei jetzt (mit nur einer Tabelle) speichern, wird sie eigentlich als Workbook — bezeichnet; es ist nur eine Arbeitsmappe mit einer Seite.

  Microsoft Office Home und Business 2016 Viewon Amazon

Einige Anwendungen benötigen möglicherweise nie mehr als eine Tabelle in Ihrer Arbeitsmappe. Wenn Ihr Excel-Wissen wächst, werden Ihre Arbeitsmappen höchstwahrscheinlich auch wachsen. Sie werden bald feststellen, dass viele Ihrer Tabellen verwandt sind oder dass sie zu groß oder zu langsam für die Verwaltung werden. Dann ist es sinnvoll, sie in einer Arbeitsmappe zusammenzufassen, was bedeutet, dass Sie mehr Arbeitsblätter hinzufügen und die Arbeitslast auf mehrere Seiten (oder Blätter) verteilen müssen. Um der Arbeitsmappe weitere Tabellen hinzuzufügen, klicken Sie auf das eingekreiste Pluszeichen am unteren Rand Ihrer Tabelle in der Taskleiste der Registerkarten. Jedes Mal, wenn Sie darauf klicken, wird eine andere Registerkarte angezeigt.

Bevor wir anfangen, ein paar Notizen über Arbeitsmappen und die Ressourcen Ihres PCs. Die Anzahl der in einer einzelnen Arbeitsmappe zulässigen Tabellenkalkulationen ist nur durch den Systemspeicher begrenzt, der sich zwischen der 32-Bit-Version (maximal 2 GB) und der 64-Bit-Version (bis 8 TB) stark unterscheidet ). Denken Sie auch daran, dass Excel über einen eigenen Speichermanager und Speicherbegrenzungen verfügt. Selbst wenn Ihr System den maximalen Arbeitsspeicher unterstützt, kann nicht garantiert werden, dass Ihre Arbeitsmappen Zugriff auf den gesamten Arbeitsspeicher haben.

Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen JD Sartain / PC World

Fügen Sie der Arbeitsmappe Tabellen hinzu, indem Sie auf das eingekreiste Pluszeichen in der Taskleiste am unteren Rand einer Arbeitsmappe klicken.

Wie Excel-Arbeitsmappen bessere Tabellen erstellen

Da die Arbeitsmappe nur die Sammelmappe enthält, die die Tabellen enthält, sind auch alle Optionen und Funktionen in einer Arbeitsmappe in der Arbeitsmappe verfügbar. Zu den produktivsten Vorteilen gehören:

  • Globale Makros — dh Makros, die auf jedem Arbeitsblatt in der Arbeitsmappe funktionieren
  • Verknüpfte Formeln
  • Relationale Daten über mehrere Tabellen verteilt und durch ein eindeutiges Schlüsselfeld verbunden
  • Tabellen werden so gruppiert, dass alle Änderungen auf alle angewendet werden.
  • Erstellen und Verwenden von Pivot-Tabellen und Zusammenfassungen
  • Drucken Berichte und Erstellen von Diagrammen aus Daten, die in mehreren Tabellen gespeichert sind

Wir werden in zukünftigen Updates auf diese Funktionen zugreifen. In dieser ersten Ausgabe zeigen wir Ihnen, wie die größten Stärken des Arbeitsbuchs in den Datenbankfunktionen und der Möglichkeit zur dreidimensionalen Berechnung von Tabellen liegen. Das bedeutet, dass Sie Spalten (nach unten), Zeilen (quer) und / oder Blätter (tief) berechnen können.

Dreidimensionale Excel-Arbeitsmappen

Das beste Beispiel, um dies zu veranschaulichen Konzept ist ein Jahr-zu-Datum-Arbeitsbuch. Die Prämisse für diese Aufgabe besteht darin, die monatlichen und jährlichen Gemeinkosten für ein kleines Unternehmen, das fünf verschiedenen Investoren gehört, zu verfolgen. Der Prozentsatz, den jeder Anleger zahlt, basiert auf seinem prozentualen Anteilsbesitz an der Gesellschaft (was auch der Prozentsatz ist, den jeder Anleger für die Gewinne des Unternehmens verdient).

Stellen Sie sich vor, Sie haben ein Blatt für jeden Monat plus ein weiteres Blatt für alle Jahresgesamtwerte. Bevor Sie all diese zusätzlichen Tabellen hinzufügen, erstellen Sie die Tabellenkalkulation „Master“ oder „Jahresgesamtumsatz“.  

1. Doppelklicken Sie auf die Registerkarte Sheet1 und ändern Sie den Namen in Summen.

2. Geben Sie das Wort Overhead in A1 ein. Geben Sie in B1 bis F1 fünf Namen ein (dies sind die Namen der fünf Eigentümer / Investoren). Geben Sie in G1 den Ausdruck Monatliche Summen ein.

3. Geben Sie in B2 bis F2 den Prozentsatz ein, den jeder Eigentümer in das Unternehmen investiert hat. Geben Sie in G2 die Summen von B2 bis F2 ein, sodass Sie sicherstellen können, dass Ihre Prozentsätze 100% nicht überschreiten. Geben Sie beispielsweise Folgendes ein: = sum (B2: F2) .

4. Geben Sie als Nächstes die Gemeinkostenposten des Unternehmens in Spalte A (von A3 bis A14) ein und fügen Sie in A15 das Wort Totals hinzu. Sie können wählen Sie Ihre eigenen Gegenstände oder nutzen Sie unsere: Ausrüstungsverleih, Internet-Service, Handy-Service, Festnetz, Strom, Wärme, Wasser, Müll / Müllabholung, Alarm-Service, Wartung, Reinigungsdienste und Landschaftsbau.

Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen JD Sartain / PC World

Richten Sie die Master-Tabellenkalkulation ein und definieren Sie sie.

Nun, da du den Master sprea hastdsheet einrichten und definieren, kopieren Sie dieses Blatt 12-mal (auf 12 neu eingefügten Tabellen).  

5. Klicken Sie mit der rechten Maustaste auf die Registerkarte Summen, und wählen Sie im Popup-Menü Verschieben oder Kopieren.

6. Klicken Sie auf Zum Ende verschieben und aktivieren Sie das Kontrollkästchen Kopie erstellen.

Kurztipp: Verwenden Sie die Strg Taste, um das Master-Arbeitsblatt mehrmals zu kopieren. Beachten Sie, dass Sie nur die verfügbaren Tabs kopieren können. Zwei kopierte Registerkarten würden insgesamt vier Registerkarten erstellen, vier Registerkarten würden insgesamt acht Registerkarten usw. erstellen. Um mehr als die verfügbaren Registerkarten zu kopieren, müssen Sie ein VBA-Programm schreiben.

7. Halten Sie die Strg-Taste gedrückt, klicken Sie dann auf die Registerkarte Summen und die Registerkarte Gesamt (2).

8. Klicken Sie erneut auf Zum Ende verschieben und aktivieren Sie das Kontrollkästchen Kopie erneut erstellen. Jetzt haben Sie vier Registerkarten.

9. Halten Sie die Taste Strg gedrückt und klicken Sie dann auf die Register Gesamt, Gesamt (2), Gesamt (3) und Gesamt (4).

10. Klicken Sie erneut auf Zum Ende verschieben und aktivieren Sie das Kontrollkästchen Kopie erneut erstellen. Jetzt haben Sie acht Registerkarten.

11. Wiederholen Sie die obigen Schritte, um vier weitere Registerkarten zu erstellen, doppelklicken Sie dann auf jede Registerkarte und geben Sie auf jeder der 12 neuen Registerkarten den Namen eines Monats ein.

Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen JD Sartain / PC World

Kopieren Sie die Master-Tabelle 12 Mal und benennen Sie dann die Tabs für jeden Monat.

Geben Sie alle Gemeinkosten für Januar in der Januar-Tabelle und bis Dezember ein. Sie können die Formeln für jede einzelne Kalkulationstabelle eingeben, z. B. die Overhead-Ausgaben-Summen in Zeile 15 (B15 bis G15) für jeden Monat, oder alle Tabellen gruppieren und diese Formeln nur einmal eingeben.

12. Bewegen Sie den Cursor in der Gesamttabelle auf die Ausgangsposition. Halten Sie dann die Taste Strg gedrückt, und klicken Sie auf die einzelnen Arbeitsblattregisterkarten (Summe zuerst, dann Jan bis Dez), um alle Blätter in dieser Arbeitsmappe zu gruppieren. Beachten Sie, dass der Tabname unterstrichen und fett dargestellt wird.

Schnelltipp: Um alle Tabellen in einer einzelnen Arbeitsmappe nacheinander zu gruppieren, klicken Sie auf die Registerkarte der ersten Tabelle und halten Sie die Umschalttaste gedrückt. Klicken Sie dann auf die Registerkarte der letzten Tabelle und alle Blätter werden gleichzeitig ausgewählt.

13. Aktuelle Position: Home (A1) in der Summen-Tabelle. Bewegen Sie den Cursor auf B15 und geben Sie folgende Formel ein: = sum (B3: B14) . Drücken Sie Enter . Sie können auch die Funktion eingeben und dann auf den Bereich zeigen.

14. Kopieren Sie die Formel in B15 bis C15 bis G15. Wenn Sie noch keine Daten in die Tabelle eingegeben haben, werden in den Summen alle Nullen angezeigt.

Noch ein schneller Tipp: Wenn Sie Ihre Zahlen in den Zellen zentrieren möchten, wäre jetzt ein guter Zeitpunkt, um die gesamte Tabelle zu formatieren, während sie gruppiert ist, also einmalig Aufgabe.

15. Markieren Sie die Tabelle und wählen Sie Mitte (links / rechts) und Mitte (oben / unten). Sie können die Zellen auch für Zahlen (mit zwei Dezimalstellen) oder als Dollar formatieren.

16. Klicken Sie sich durch die Registerkarten am unteren Rand und überprüfen Sie, ob Sie ALLE Registerkarten gruppiert haben. Wenn alle Formeln und Formate vorhanden und identisch sind, war Ihre Gruppe erfolgreich.

Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen JD Sartain / PC World

Gruppieren Sie alle Registerkarten, geben Sie die Spalten- und Zeilenformeln ein und formatieren Sie die Daten.

17. Geben Sie als Nächstes einige Daten in der Spalte „Monatliche Summen“ (G) in den Tabellen Jan und Feb ein. Dies wäre der Gesamtbetrag, der für jeden Gemeinkostenposten für den aktuellen Monat gezahlt wird.

Sobald die aktuellen monatlichen Summen eingegeben sind, können wir die prozentualen Formeln hinzufügen, die den Betrag bestimmen, den jeder Besitzer zahlt. Zum Beispiel, wenn die monatlichen Gesamtkosten für Equipment Rentals 9500,00 $ beträgt, beträgt der Anteil von Jackson 31% dieses Betrags.

18. Gruppieren Sie die monatlichen Kalkulationstabellen erneut, aber fügen Sie dieses Mal NICHT das Summenblatt hinzu. Geben Sie die prozentualen Formeln ein.

Hinweis: Verwenden Sie die Funktionstaste F4, um die Zellenadressen zu ändern, die sich nicht absolut ändern. Dies bedeutet, dass sich die Zellenadresse beim Kopieren der Formel nicht ändert Referenz). Relative Verweise, die in Excel standardmäßig verwendet werden, bedeuten, dass beim Kopieren der Formeln die Zelladressen in der Formel basierend auf der relativen Position der Spalten und Zeilen geändert werden.

19. Geben Sie die folgende Formel in Zelle B3 ein: = SUM ($ G3 * B $ 2) . Kopieren Sie diese Formel in B4 bis F14.

20. Markieren Sie die vollständige Tabelle und wählen Sie das $ Format.

Die Spalten- und Zeilenformeln werden eingegeben und funktionieren ordnungsgemäß. Geben Sie nun die Tiefe oder 3D-Formeln ein. Diese Formeln berechnen die Daten von jedem Monat (Januar bis Dezember) und drucken die Antwort auf dem Summenblatt.

21. Kehren Sie zur Gesamttabelle zurück und geben Sie diese Formel in Zelle B3 ein: = SUM (Jan: Dec! B3) .

22. Kopieren Sie diese Formel zwischen B3 und C3 durch G3. Markieren Sie dann diese Zeile und kopieren Sie sie nach unten in B4 bis G14.

23. Markieren Sie den Bereich, wählen Sie das Format $ und passen Sie t anDie Spaltenbreite aller Spalten, die eine Kette von Nummernzeichen (Hash-Tags) anzeigen.

Excel-Arbeitsmappen: Dreidimensionale Arbeitsmappen und andere Rechenfunktionen JD Sartain / PC Welt

Die vollständige Arbeitsmappe mit Jahresangaben mit Formeln, die vertikal, horizontal und dimensional berechnet werden

Sobald diese Arbeitsmappe festgelegt ist oben und die Formeln sind vorhanden, Sie können es für immer wiederverwenden. Kopieren Sie es einfach und ändern Sie die Daten, was einfach ist, da sich die einzigen Zellen mit dynamischen Daten in Spalte G, Monatliche Summen befinden. Alles andere ist ein Etikett oder eine Berechnung.

Wichtiger Hinweis: Wenn Sie weitere Gemeinkostenelemente hinzufügen (oder löschen) müssen, verwenden Sie den Befehl Einfügen > Sheet Row-Funktion und stellen Sie sicher, dass Sie die neuen Zeilen INSIDE in die vorhandene Tabelle einfügen, dh vor dem letzten Datensatz und nach dem ersten Datensatz. Fügen Sie beispielsweise neue Zeilen zwischen A4 und A13 ein. Wenn Sie neue Zeilen außerhalb des definierten Bereichs einfügen, werden diese Zeilen NICHT in die vorhandenen Formeln eingeschlossen oder berechnet, was bedeutet, dass alle Ihre Summen falsch sind.

Verwenden Sie die gleiche Logik zum Einfügen von Spalten und zusätzlichen Blättern. Sobald die neuen Zeilen eingefügt sind, können Sie den Bereich immer neu sortieren, wenn Ihnen die Reihenfolge der neuen Daten nicht gefällt.

q , quelle

Zusammenhängende Posts:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.