Konsequente Excel -Erstellung, Datenarbeit, Entfernen

Excel ist ein ziemlich leistungsfähiges Tool, mit dem Sie mit großen Datenarrays arbeiten und eine Reihe von Vorgängen mit ihnen ausführen können. Es kann argumentiert werden, dass Excel -Tabellen relationale Datenbanken sind, mit denen Sie arbeiten können, ohne die Fähigkeiten eines Programmierers zu besitzen. Wenn das Programm jedoch professionell verwendet wird, reichen grundlegende Funktionen nicht aus, um Berichte über verschiedene Strukturen zu erstellen. Für diese Zwecke verfügt der Tablist -Prozessor über ein funktionaleres und leistungsfähigeres Werkzeug - Zusammenfassungstabellen.

Warum brauchen Excel zusammenfassende Tabellen?

Im Original klingt dieser Begriff wie eine Pivot -Tabelle, und die Übersetzung des Ausdrucks ist etwas neblig: "Eine Tabelle, die verändert, verdreht, in verschiedenen Projektionen demonstriert werden kann". Was bedeutet das? Interpretation der tabellarischen Daten - Die Aufgabe ist nicht einfach. Dies erfolgt normalerweise mit Formeln, wobei die endgültigen Ergebnisse am Ende der Tabelle erhalten werden.

Wenn Sie komplexe Berichte mit Filtern erstellen und die Quelldaten in der richtigen Reihenfolge ändern müssen, machen Sie diese mit Standard -Excel -Mitteln, wenn möglich, sehr schwierig. Dank konsolidierter Tabellen sind solche Berichte jedoch viel einfacher zu erstellen, z.

Die meisten Excel -regulären Benutzer sind sich der Anwesenheit eines so leistungsstarken Tools nicht einmal bewusst. Aus Gründen der Klarheit geben wir ein kleines und indikatives Beispiel für die Verwendung der Excel -konsolidierten Tabellen "für Dummies".

Angenommen, Sie sind Unternehmensleiter und für die Aufrechterhaltung der Verkaufsdokumentation verantwortlich. Alle Transaktionen werden in die Tabelle eingegeben, in der alle Details angegeben sind (Verkäufer, Käufer, Menge, Betrag, Verkaufsdatum, Käuferadresse usw. D.). Der Leiter bittet um einen Bericht über den Umsatz nach Region.

Wie wir es machen? Wir erstellen ein Layout einer neuen Tabelle, in der wir die gesamte Verkaufsspanne angeben, und in den Zeilen - die Namen der Regionen. Dazu kopieren wir aus der Quelltabelle eine Spalte mit Waren und löschen Sie Duplikate. Mit dem Modus eines speziellen Einsatzes verwandeln wir diese Spalte in einen Hut der konsolidierten Seite. Wir kommen mit den Regionen auf die gleiche Weise, aber da sie sich vertikal in der konsolidierten Tabelle befinden, kopieren wir einfach ohne Transponieren.

In jeder Zelle fassen wir die Einnahmen anhand der Funktion des Summails zusammen. Fügen Sie schließlich die Endergebnisse nach unten hinzu und senden Sie den Bericht an den Manager. Der Führer bittet die Regionen, sich oben und die Ware auf den Linien zu arrangieren. Sie erstellen in 5 Minuten einen neuen Bericht und erhalten eine neue Aufgabe: Gewinndaten, nicht um Einnahmen, dh unter Berücksichtigung der Kosten. Nun, wir ändern die Formel ein wenig und das Ergebnis ist wieder in Minuten fertig. Schließlich gibt es eine Anfrage, solche Berichte für jeden Verkäufer vorzubereiten, um seine Effektivität zu bewerten. Und dies ist kein Problem für eine Zusammenfassungstabelle, wenn Sie wissen, wo sich die erforderlichen Anfangsdaten befinden!

In der Zwischenzeit weiß jeder Excel -Benutzer, wie schwierig es ist, solche Manipulationen im Rahmen der Quellentabelle auszuführen.

Schöpfung und Arbeit mit konsolidierten Tischen in Excel

Nehmen wir an, wir haben die Verkaufstabelle der Verkäufe:

Überlegen Sie, wie Sie auf der Grundlage dieser tabellarischen Daten eine konsolidierte Tabelle erstellen können. Wählen Sie dazu eine Zelle aus, nach der wir die Registerkarte "Einfügen" im Hauptfeld auswählen. Es werden zwei zusätzliche Registerkarten angezeigt: "Zusammenfassungstabelle" und links - "Empfohlene Zusammenfassungstabellen". Wenn Sie Anfänger sind und nicht ganz verstehen, wie Sie die Quelldaten organisieren können, empfehlen wir die Verwendung der zweiten Taste. Dies ist ein Meister, der Ihnen bereitgestellte Berichtsoptionen anbietet. Sie müssen nur die entsprechende Option auswählen und die fertige konsolidierte Tabelle zur Verfügung stellen. Es bleibt nur, sie zu korrigieren, wenn sie Sie nach einigen Kriterien nicht zufriedenstellt.

Die zweite Hauptmethode zum Erstellen konsolidierter Tabellen ist "von Grund auf", für die wir die erste Taste drücken.

Ein neues Fenster wird geöffnet, in dem zwei Parameter festgelegt werden müssen: den Anfangsbereich der Zelle und die Position des ST. Da wir vor dem Erstellen einer Pivot -Tabelle eine Zelle ausgewählt haben, wird der Bereich automatisch bestimmt. Der Standardwert für den zweiten Parameter ist ein neues Blatt. Wir ändern also nichts und klicken auf OK. Infolgedessen wird ein neues Blatt mit einem unvollständigen Layout geöffnet.

Um es zu konfigurieren, verwenden Sie das rechte Feld mit dem Titel "Feld der Zusammenfassungstabelle" mit dem Titel "Feld der Zusammenfassung". Hier in der oberen Einheit ist eine Liste der verfügbaren Felder, die der Spalte der Quelltabelle entsprechen, aufgeführt. Sie können Felder auf zwei Arten hinzufügen: Indem Sie ein Checkmark gegenüber dem gewünschten Wert einlegen, und dann automatisch in das Layout eingeht. Aber weit weg immer zu dem Ort, den Sie brauchen. In solchen Fällen müssen Sie nur das Feld in die erforderliche Position des Layouts ziehen. Das Entfernen einer Position im Layout wird auf ähnliche Weise durchgeführt, das Checkmark entfernen oder das rechte Feld schleppen.

Es enthält vier unten stehende Regionen, auf deren Grundlage eine Zusammenfassungstabelle gebildet wird. Betrachten Sie sie ausführlicher:

• Der Wertebereich ist der Hauptteil der ST, da hier angegeben wird, wie Spalten und Linien verarbeitet werden. Normalerweise ist dies eine Summe - diese Aggregationsmethode wird standardmäßig festgelegt, sofern alle Zellen des Bereichs numerische Werte akzeptieren. In Gegenwart mindestens einer leeren Zelle oder mit Daten in einem Format, das sich von einer numerischen anstelle der Summe der Zellwerte unterscheidet, wird ihre Zahl berechnet, sodass diese Tatsache berücksichtigt werden muss. Für unser Beispiel wird für jedes Produkt in der entsprechenden Region eine Summierung durchgeführt. Zusätzlich zur Summierung können Sie jede andere Berechnungsmethode festlegen, die etwa zwei Dutzend aufweisen (Berechnung des Durchschnittswerts, der Aktien usw.). Der einfachste Weg, dies zu tun, ist direkt in der Zusammenfassung der Tabelle, die auf PKM für jede Zelle klickt und die erforderliche Berechnungsmethode auswählt
• Der Bereich der Linien - besteht aus einzigartigen Werten von Zellen in der linken Säule. Wenn Sie hier mehrere Felder einfügen, ist die Zusammenfassungstabelle nicht mehr flach, sondern mehrfach
• Der Spaltenbereich enthält auch Feldwerte, jedoch bereits relativ zur oberen Zeile der Ausgangstabelle
• Mit dem Filterbereich können Sie Einschränkungen für die Berechnung der Werte der Zellen auferlegen. In der Regel wird hier ein Feld platziert, wonach eine Stichprobe in drei anderen Bereichen gefiltert werden sollte.

Dank der Anwesenheit dieser Bereiche können Sie ein Beispiel aus der Ausgangstabelle fast jeder Komplexität konfigurieren und einige Minuten verbringen.

Um es klarer zu machen, erläutern wir das obige anhand eines im vorherigen Abschnitts beschriebenen Beispiels.

Also, Schritt -by -STEP -Anweisungen zur Erstellung einer konsolidierten Tabelle in Excel:

• Aus der Liste der Felder in der zusammenfassenden Tabelle ziehen wir das Feld "Einnahmen" in den Wertebereich
• Das Feld "Region" (die Region des Käufers) wird in die Linien gezogen;
• Feld "Produkt" in den Spaltenbereich eingebracht.

Nur drei Aktionen - und eine zusammenfassende Tabelle, die zum Senden an die Behörden geeignet ist, ist bereit! Wir haben ungefähr 10 Sekunden gebraucht, um es zusammenzustellen.

Wie man die anderen Wünsche des Direktors erfüllt?

Um Gewinn anstelle von Einnahmen zu erzielen, reicht es aus, das entsprechende Feld in der Wertefläche anstelle von Einnahmen zu platzieren, die entsprechenden Berechnungen in der Zusammenfassung automatisch erstellt.

Die zweite Aufgabe wird auch in Sekunden ausgeführt - um die Regionen und Waren zu ändern, sie einfach von einem Bereich in einen anderen zu ziehen (Orte ändern).

Um die Ergebnisse für jeden Manager zu erhalten, reicht es aus, das Feld "Manager" im Filterbereich zu platzieren. Anschließend erscheint ein Selektor oben, in dem Sie den gewünschten Nachnamen angeben müssen, um einen Bericht zu erhalten. Es gibt die Möglichkeit, einen Bericht über mehrere Verkäufer zu kombinieren.

Natürlich haben wir nicht das schwierigste, aber ein sehr indisches Beispiel, mit dem Sie verstehen können, wie Sie zusammenfassende Tabellen in Excel erstellen können. Die Methode zur Berechnung der Werte in den Zellen kann komplizierter sein. Möglicherweise müssen Sie die berechneten Felder verwenden und eine bedingte Formatierung anwenden - die diesbezüglichen Möglichkeiten in Excel sind beeindruckend.

Über die Datenquelle

Wir haben bereits erwähnt, dass die anfänglichen Daten bestimmte Anforderungen erhoben werden, mit denen konsolidierte Tabellen im Excel -Programm korrekt erstellt wurden. Lassen Sie uns sie auflisten:

• Die Hauptbeschränkung ist mit dem obligatorischen Vorhandensein von Namen gegenüber Spalten verbunden, die an den Berechnungen beteiligt sind. Solche Kennungen sind für die Bildung resultierender Berichte erforderlich - beim Hinzufügen neuer Datensätze (Zeichenfolgen) zur Ausgangstabelle ist das Format nicht erforderlich, um das ST -Format zu ändern, und die Ergebnisse werden automatisch neu berechnet
• Stellen Sie sicher, dass in den Zellen der an der Probe beteiligten Zellen der Linien und Säulen numerische Parameter eingeführt werden. Wenn sie leer sind oder Textwerte enthalten, fallen diese Zeilen aus den Berechnungen heraus, wodurch die Ergebnisse der Berechnungen verzerrt werden
• Folgen Sie der Entsprechung der Formate der Linien und dem Inhalt der Zellen. Wenn es als Datum definiert ist, müssen alle Werte in der Spalte das gleiche Format haben.

Wenn Sie sich an diese Regeln halten, sollte sich bei der Konstruktion einer Zusammenfassungstabelle Probleme nicht ergeben.

Datenaktualisierung in der Excel -Set -Tabelle

Obwohl wir über die automatische Neuberechnung von konsolidierten Tabellen gesprochen haben.

Sie können die Daten in unserer Expositionstabelle auf zwei Arten aktualisieren:

• Klicken Sie mit der rechten Maustaste in einer beliebigen Zelle und Auswahl des Elements "Aktualisieren" im Kontextmenü
• Verwenden der Registerkarte "Daten" und der Schaltfläche "Alles aktualisieren" ".

Das Ausschneiden von Tabellen ist besonders gerechtfertigt, wenn sie groß sind. In diesem Fall wird die Geschwindigkeit des Programms erheblich zunehmen, insbesondere wenn Sie regelmäßig neue Linien eingeben.

Hinzufügen einer Spalte oder einer Zeile zum Excel

Im Allgemeinen ist die Operation trivial, wenn sich der gewünschte Parameter in der Quellentabelle befindet. Sie müssen nur das rechte Feld in den richtigen Bereich ziehen. Wenn in der Ausgangstabelle keine Zeichenfolge oder Spalte vorhanden ist, geben Sie sie bei Bedarf hin, wobei Sie es eingeben. Klicken Sie anschließend auf die Registerkarte "Analyse" und öffnen Sie unsere geänderte Tabelle. Es bleibt nur die Aktualisierung der Daten (siehe. Der vorherige Abschnitt) ändert sich infolge.

Oft hat die Zusammenfassungstabelle keine bequemen Spezies für die Visualisierung. Zum Beispiel, wenn es mehrere Felder in den Zeilen gibt. Dann wird einer von ihnen vollständig angezeigt, und die Werte des anderen müssen im oberen Teil der Tabelle angezeigt werden. Wenn es nur wenige solche Werte gibt (z. B. in unserem Beispiel von Verkaufsregionen nur 6), ist es sinnvoll, dem entsprechenden Bereich eine Spalte "Regionen" der Pivot -Tabelle hinzuzufügen und sie aus dem Bereich des Gebiets zu übertragen Linien.

Das Ändern der Prozedur für die Befolgung der Spalten ist ebenfalls leicht zu erreichen. Übertragen Sie einfach die Spaltenüberschrift mit der Maus in die gewünschte Position.

Bildung einer zusammenfassenden Tabelle aus mehreren Dateien (Tabellen) Excel

Dies ist eine kompliziertere, aber ziemlich vollständige Aufgabe. Verwenden Sie den Master ST, der dem schnellen Zugriffsfeld hinzugefügt werden muss.

Klicken Sie also links in der Nähe des Elements "Datei" auf den nach unten gerichteten Pfeil das Element "Andere Befehle" aus. Ein neues Fenster wird im mittleren Feld geöffnet, klicken wir auf die Optionen "Alle Teams". Wir finden in der Liste der geöffneten Liste „Master of Consolidated Tabellen“, wählen Sie es aus und klicken Sie auf die Schaltfläche „Hinzufügen“. Das entsprechende Symbol wird im oberen Feld angezeigt.

Wir starten den Master. Angenommen, wir müssen Daten aus zwei Blättern im Mai und im Juni kombinieren. Die Hauptbedingung ist, dass sie in der Struktur zusammenfallen müssen. Wenn ja, dann wählen wir im ersten Fenster des Masters die Option „Substanztabelle“ und den nächsten Parameter aus - „Mehrere Konsolidierungsbereiche“.

Im zweiten Schritt geben wir dem Meister an, dass wir mehrere Felder brauchen. Beim dritten - Wählen Sie den Zellbereich für das Summieren des ersten Blattes aus, klicken Sie auf "Hinzufügen" und tun Sie dasselbe für das zweite Blatt -Excel. Jetzt müssen Sie die Namen an zwei Felder geben. Wir machen dies zuerst für das erste Feld (klicken. In diesem Fall muss in der Liste der Bereiche die entsprechende Linie, die erste oder zweite, angegeben werden.

Nachdem wir auf die Schaltfläche "Ready" geklickt haben, wird ein neues Fenster geöffnet, in dem wir das "neue Blatt" angeben und die Schaltfläche "Ready" erneut drücken.

AUFMERKSAMKEIT. Der Erhalt mehrdimensionaler Tabellen ist unpraktisch, da es schwierig ist, mit einer großen Anzahl von Feldern zu navigieren, und die erforderlichen Berechnungen müssen jedes Mal durchgeführt werden, was die erforderlichen Werte aus der oberen Fläche der Tabelle angibt.

Wie man Kunst entfernen

Der einfachste Fall - als Sie eine konsolidierte Tabelle erstellt haben, haben die Ergebnisse an den Küchenchef gesendet und benötigen sie nicht mehr. Wenn Sie sich dessen sicher sind, wählen Sie einfach die Tabelle aus und klicken Sie auf die Taste Löschen. Einfach und effektiv.

Aber plötzlich kann die Struktur der Tabelle in Zukunft benötigt werden? Excel hat die Möglichkeit, nur die Ergebnisse oder Daten aus Zellen zu löschen. Überlegen Sie, wie dies getan wird.

Um die Berechnungsergebnisse zu löschen, führen wir die folgenden Schritte aus:

• Wir markieren jede Zelle von ST;
• Wählen Sie die Registerkarte "Analyse" im oberen Feld
• Wählen Sie aus der Liste der verfügbaren Aktionen "Clear" aus
• Klicken Sie auf den Punkt "alles löschen".

Aber was zu tun ist, wenn Sie die Ergebnisse speichern möchten, aber Sie brauchen selbst keine Daten, das heißt, Sie möchten die Tabelle befreien? Diese Situation entsteht oft, wenn die Führung nur Ergebnisse benötigt. Der Algorithmus der Handlungen:

• Wählen Sie erneut eine Zelle aus, klicken Sie auf die Registerkarte "Analyse"
• Wählen Sie das Menüpunkt "Aktion" aus, klicken Sie auf "Wählen" und markieren Sie die gesamte konsolidierte Tabelle mit der Maus
• Klicken Sie auf den PCM im ausgewählten Bereich
• Wählen Sie im Kontextmenü das Element "Kopieren" aus
• Wir gehen auf die Registerkarte "Home", klicken erneut auf PKM und wählen "Einfügen"
• Wählen Sie die Registerkarte "Wert einfügen.

Infolgedessen wird die Zusammenfassungstabelle gelöscht, während die Ergebnisse beibehalten werden.

BERATUNG. Sie können die Prozedur durch die Schlüsselkombination beschleunigen. Um die Tabelle auszuwählen, verwenden Sie Strg + A zum Kopieren - Strg + C. Drücken Sie dann Alt + E, Alt + S, Alt + V und vervollständigen Sie die Prozedur, indem Sie die Eingabetaste drücken.

Um konsolidierte Tabellen in Excel 2007/2010 zu entfernen, müssen Sie einen anderen Algorithmus verwenden:

• ST ST für das Entfernen ausgewiesen;
• Klicken Sie auf die Registerkarte "Parameter";
• Wählen Sie in der "Gruppe von Aktionen" das "saubere" Element aus und dann "alles reinigen".

Wenn Ihr Chef die Datenvisualisierung liebt, ist es offensichtlich, dass Sie konsolidierte Diagramme verwenden müssen. Da sie viel Platz im Tisch belegen, werden sie nach der Verwendung normalerweise entfernt.

In den alten Versionen des Programms müssen Sie dafür ein Diagramm auswählen, klicken Sie auf die Registerkarte "Analyse", wählen Sie eine Datengruppe aus und klicken Sie auf "Reinigen" und "alles reinigen".

Wenn das Diagramm mit der summarischsten Tabelle zugeordnet ist, verlieren Sie nach seiner Löschung alle Einstellungen der Tabelle, der Felder und der Formatierung.

Für Versionen, die älter als Excel 2010 sind, müssen Sie ein Diagramm auswählen, "Aktionen" auf der Registerkarte "Analyse" auswählen und auf "Reinigen" klicken und alles reinigen ". Das Ergebnis wird ähnlich sein.

Wir hoffen, dass unsere Lektionen auf konsolidierten Tabellen ermöglichen, diese ausreichend leistungsfähige Funktionalität zu entdecken. Wenn Sie noch Fragen haben, stellen Sie sie in den Kommentaren, wir werden versuchen, sie zu beantworten.