Praktische Arbeit an der Olap-Technologie in Excel. Olap-Würfel in Excel. Erforderliche Softwarekomponenten für den Zugriff auf OLAP-Datenquellen

OLAP-Client-Tools sind Anwendungen, die aggregierte Daten berechnen (Summen, Durchschnitte, Maxima bzw Mindestwerte) und deren Anzeige, während die aggregierten Daten selbst im Adressraum eines solchen OLAP-Tools zwischengespeichert werden.

Wenn die Quelldaten in einem Desktop-DBMS enthalten sind, wird die Berechnung der aggregierten Daten vom OLAP-Tool selbst durchgeführt. Wenn die Quelle der Quelldaten ein Server-DBMS ist, senden viele der Client-OLAP-Tools SQL-Abfragen mit der GROUP BY-Klausel an den Server und erhalten als Ergebnis aggregierte Daten, die auf dem Server berechnet wurden.

Die OLAP-Funktionalität ist in der Regel in Tools implementiert statistische Verarbeitung Daten (von Produkten dieser Klasse auf dem russischen Markt sind Produkte von StatSoft und SPSS weit verbreitet) und in einigen Tabellenkalkulationen. Insbesondere hat sich das Mittel der multivariaten Analyse bewährt Microsoft Excel. Mit diesem Produkt können Sie einen kleinen lokalen multidimensionalen OLAP-Würfel erstellen und als Datei speichern und seine 2D- oder 3D-Ausschnitte anzeigen.

Datenextraktions- und -verarbeitungs-Add-Ins für Microsoft Office sind eine Reihe von Funktionen, die Zugriff auf die Datenextraktions- und -verarbeitungsfunktionen von Microsoft Office-Anwendungen bieten und dadurch eine vorausschauende Analyse ermöglichen lokalen Computer. Aufgrund der Tatsache, dass die integrierten Dienste der Microsoft-Plattform SQL Server Mit Datenextraktions- und Verarbeitungsalgorithmen, die in der Microsoft Office-Anwendungsumgebung verfügbar sind, können Geschäftsanwender mit nur wenigen Klicks wertvolle Erkenntnisse aus komplexen Datensätzen extrahieren. Add-Ons für die Datenextraktion und -verarbeitung der Office-Suite ermöglichen es Endbenutzern, Analysen direkt in Microsoft-Anwendungen Excel und Microsoft Visio.

BEI Microsoft-Aufstellung Office 2007 enthält drei separate OLAP-Komponenten:

1. Mit dem Datenextraktions- und -verarbeitungsclient für Excel können Sie SSAS-basierte Datenextraktions- und -verarbeitungsprojekte in Excel 2007 erstellen und verwalten;
2. Spreadsheet-Analysetools für Excel ermöglichen es Ihnen, die integrierten Informationsabruf- und -verarbeitungsfunktionen von SSAS zu verwenden, um in Excel-Tabellen gespeicherte Daten zu analysieren.
3. Datenextraktions- und Bearbeitungsvorlagen für die Visio-Anwendung ermöglichen Ihnen die Visualisierung von Entscheidungsbäumen, Regressionsbäumen, Clusterdiagrammen und Abhängigkeitsnetzwerken in Visio-Diagrammen.

Tabelle 1.1. Oracle-Produkte für OLAP und Business Intelligence

Art des Fonds	Produkt

In einer Standard-PivotTable werden die Quelldaten auf der lokalen Festplatte gespeichert. Auf diese Weise können Sie sie jederzeit verwalten und neu organisieren, auch wenn Sie keinen Zugriff auf das Netzwerk haben. Dies gilt jedoch in keiner Weise für OLAP-PivotTables. In OLAP-PivotTables wird der Cache nie auf der lokalen Festplatte gespeichert. Daher sofort nach dem Trennen von lokales Netzwerk Ihre Pivot-Tabelle wird fehlschlagen. Sie können keines der darin enthaltenen Felder verschieben.

Wenn Sie OLAP-Daten noch analysieren müssen, nachdem Sie offline gegangen sind, erstellen Sie einen Offline-Datenwürfel. Ein Offline-Datencube ist eine separate Datei, die ein PivotTable-Cache ist und OLAP-Daten speichert, die angezeigt werden, nachdem sie vom lokalen Netzwerk getrennt wurden. In eine Pivot-Tabelle kopierte OLAP-Daten können ausgedruckt werden, die Seite http://everest.ua beschreibt dies ausführlich.

Um einen eigenständigen Datencube zu erstellen, erstellen Sie zunächst eine OLAP-PivotTable. Platzieren Sie den Cursor in der PivotTable und klicken Sie auf die Schaltfläche OLAP-Tools auf der kontextabhängigen Registerkarte Tools, die Teil der kontextabhängigen Registerkartengruppe PivotTable-Tools ist. Wählen Sie den Befehl Offline OLAP (Abb. 9.8).

Reis. 9.8. Erstellen Sie einen Offline-Datenwürfel

Das Dialogfeld „Offline-Cube-Einstellungen“ wird auf dem Bildschirm angezeigt. OLAP-Daten. Klicken Sie auf die Schaltfläche Offline-Datendatei erstellen. Sie haben den Assistenten zum Erstellen einer Datenwürfeldatei gestartet. Klicken Sie auf die Schaltfläche Weiter, um den Vorgang fortzusetzen.

Zuerst müssen Sie die Dimensionen und Ebenen angeben, die in den Datenwürfel aufgenommen werden. Im Dialogfenster müssen Sie die Daten auswählen, die aus der OLAP-Datenbank importiert werden. Die Idee besteht darin, nur die Dimensionen anzugeben, die benötigt werden, nachdem der Computer vom lokalen Netzwerk getrennt wurde. Je mehr Dimensionen Sie angeben, desto größer wird der Offline-Datenwürfel.

Klicken Sie auf die Schaltfläche Weiter, um mit dem nächsten Dialogfeld des Assistenten fortzufahren. Es gibt Ihnen die Möglichkeit, Member oder Datenelemente anzugeben, die nicht in den Cube aufgenommen werden. Insbesondere benötigen Sie das Measure „Internet Sales – Extended Amount“ nicht, daher ist es in der Liste deaktiviert. Ein deaktiviertes Kontrollkästchen zeigt an, dass das angegebene Element nicht importiert wird und zusätzlichen Speicherplatz auf der lokalen Festplatte belegt.

Geben Sie im letzten Schritt den Ort und den Namen des Datenwürfels an. In unserem Fall heißt die Cube-Datei MyOfflineCube.cub und befindet sich im Arbeitsordner.

Datenwürfeldateien haben die Erweiterung .cub

Nach einer Weile speichert Excel den Offline-Datenwürfel im angegebenen Ordner. Um es zu testen, doppelklicken Sie auf die Datei, wodurch automatisch eine Excel-Arbeitsmappe generiert wird, die eine PivotTable enthält, die dem ausgewählten Datenwürfel zugeordnet ist. Nach der Erstellung können Sie den Offline-Datenwürfel an alle interessierten Benutzer verteilen, die im Offline-LAN-Modus arbeiten.

Sobald Sie mit dem lokalen Netzwerk verbunden sind, können Sie die Offline-Datenwürfeldatei öffnen und sie sowie die entsprechende Datentabelle aktualisieren. Das Hauptprinzip ist, dass der Offline-Datenwürfel nur für die Arbeit verwendet wird, wenn das lokale Netzwerk getrennt ist, aber es ist zwingend erforderlich, ihn zu aktualisieren, nachdem die Verbindung wiederhergestellt wurde. Der Versuch, einen Offline-Datenwürfel zu aktualisieren, nachdem die Verbindung unterbrochen wurde, schlägt fehl.

Arbeiten mit Offline-Cube-Dateien

Eine Offline-Cube-Datei (.cub) speichert Daten in Form eines OLAP-Cubes (Online Analytical Processing). Diese Daten können Teil der OLAP-Datenbank auf dem OLAP-Server sein oder unabhängig von der OLAP-Datenbank generiert werden. Verwenden Sie eine Offline-Cube-Datei, um weiterhin mit PivotTable- und PivotChart-Berichten zu arbeiten, wenn der Server nicht verfügbar ist oder wenn Sie offline sind.

Sicherheitshinweis: Seien Sie vorsichtig, wenn Sie eine Offline-Cube-Datei verwenden oder verteilen, die vertrauliche oder persönliche Daten enthält. Anstelle einer Cube-Datei empfiehlt es sich, die Daten in einer Arbeitsmappe zu speichern, damit Sie den Zugriff auf die Daten über die Rechteverwaltung steuern können. Weitere Informationen finden Sie unter Verwaltung von Informationsrechten in Office.

Wenn Sie mit einem PivotTable- oder PivotChart-Bericht arbeiten, der auf OLAP Server-Quelldaten basiert, können Sie den Offline-Cube-Assistenten verwenden, um die Quelldaten in eine separate Offline-Cube-Datei auf Ihrem Computer zu kopieren. Zum Erstellen dieser Offlinedateien ist ein OLAP-Datenanbieter erforderlich, der diese Funktion unterstützt, z. B. MSOLAP von Microsoft SQL Server Analysis Services, der auf dem Computer installiert ist.

Notiz: Die Erstellung und Verwendung von Offline-Cube-Dateien von Microsoft SQL Server Analysis Services unterliegt der Laufzeit und der Lizenzierung Microsoft-Installationen SQL Server. Überprüfen Sie die relevanten Lizenzierungsinformationen für die SQL Server-Edition.

Arbeiten mit dem Offline-Cube-Assistenten

Um eine Offline-Cube-Datei zu erstellen, können Sie mit dem Offline-Cube-Assistenten eine Teilmenge von Daten in einer OLAP-Datenbank auswählen und diese Teilmenge dann speichern. Der Bericht muss weder alle in der Datei enthaltenen Felder enthalten, noch muss er eines davon und die in der OLAP-Datenbank verfügbaren Datenfelder auswählen. Um die Datei möglichst klein zu halten, können Sie nur die Daten einbeziehen, die im Bericht erscheinen sollen. Sie können alle Dimensionen auslassen, und für die meisten Dimensionstypen können Sie auch Details und Elemente auf niedrigerer Ebene ausschließen. Höchststufe die nicht angezeigt werden müssen. Für alle von Ihnen eingeschlossenen Elemente werden die in der Datenbank für diese Elemente verfügbaren Eigenschaftsfelder auch in der Offlinedatei gespeichert.

Übertragen von Daten an Offline-Modus und ihre umgekehrte Verbindung

Dazu müssen Sie zunächst einen PivotTable- oder PivotChart-Bericht basierend auf der Serverdatenbank erstellen und dann aus dem Bericht eine eigenständige Cube-Datei erstellen. Danach können Sie den Bericht jederzeit zwischen der Server-Datenbank und der Offline-Datei umschalten. Wenn Sie zum Beispiel verwenden Laptop für Heim- und Videoreisen, und verbinden Sie Ihren Computer dann erneut mit dem Netzwerk.

Im Folgenden sind die wichtigsten Schritte aufgeführt Lebensdauer der Batterie mit den Daten und übertragen Sie die Daten dann zurück ins Internet.

Erstellen oder öffnen Sie einen PivotTable- oder PivotChart-Bericht basierend auf den OLAP-Daten, auf die Sie offline zugreifen möchten.

Erstellen Sie eine Offline-Cube-Datei auf Ihrem Computer. Im Kapitel Erstellen Sie eine Offline-Cube-Datei aus einer OLAP-Server-Datenbank(unten in diesem Artikel).

Trennen der Verbindung zum Netzwerk und Arbeiten mit einer Offline-Cube-Datei.

Gehen Sie online und hängen Sie die Cube-Datei offline erneut an. Sehen Sie sich den Abschnitt an Wiederverbindung Offline-Cube-Datei in die OLAP-Server-Datenbank(unten in diesem Artikel).

Aktualisieren Sie die Offline-Cube-Datei mit neuen Daten und generieren Sie die Offline-Cube-Datei neu. Sehen Sie sich den Abschnitt an Aktualisieren Sie die Offline-Cube-Datei und erstellen Sie sie neu(unten in diesem Artikel).

BLOG

Nur Qualitätsbeiträge

Was sind Excel-PivotTables und OLAP-Cubes?

Sehen Sie sich das Video zum Artikel an:

OLAP- das ist Englisch. analytische Online-Verarbeitung, analytische Datenverarbeitungstechnologie in Echtzeit. Einfach ausgedrückt - ein Speicher mit mehrdimensionalen Daten (Cube), noch einfacher - nur eine Datenbank, aus der Sie Daten in Excel abrufen und mit dem Excel-Tool - PivotTables - analysieren können.

Pivot-Tabellen- Das Benutzeroberfläche mehrdimensionale Daten anzuzeigen. Mit anderen Worten - eine besondere Art von Tabellen, mit denen Sie fast jeden Bericht erstellen können.

Vergleichen wir zur Verdeutlichung die "Stammtabelle" mit der "Pivot-Tabelle".

Stammtisch:

Übersichtstabelle:

Hauptunterschied Pivot-Tabellen ist das Vorhandensein eines Fensters Feldliste der Pivot-Tabelle“, aus dem Sie die gewünschten Felder auswählen können und automatisch eine beliebige Tabelle erhalten!

Wie benutzt man

offen Excel-Datei, die mit dem OLAP-Cube verbunden ist, zum Beispiel "BIWEB":

Was bedeutet das nun und wie benutzt man es?

Ziehen Sie die erforderlichen Felder per Drag-and-Drop, um beispielsweise die folgende Tabelle zu erhalten:

« Pluspunkte» ermöglichen es Ihnen, einen Drilldown in den Bericht durchzuführen. In diesem Beispiel wird „Marke“ auf „Abgekürzte Namen“ und „Quartal“ auf „Monat“ aufgeschlüsselt, d. h. So:

Analytische Funktionen in Excel (Würfelfunktionen)

Microsoft erweitert Excel ständig um neue Funktionen in Bezug auf Datenanalyse und Visualisierung. Das Arbeiten mit Informationen in Excel kann als drei relativ unabhängige Ebenen dargestellt werden:

• "richtig" organisierte Quelldaten
• Mathematik (Logik) der Datenverarbeitung
• Daten Präsentation

Reis. 1. Datenanalyse in Excel: a) Rohdaten, b) Messung in Power Pivot, c) Dashboard; Um ein Bild zu vergrößern, klicken Sie mit der rechten Maustaste darauf und wählen Sie es aus Öffne das Bild in einem neuen Tab

Notiz herunterladen in Word-Format oder pdf, Excel-Beispiele

Cube-Funktionen und Pivot-Tabellen

Das einfachste und gleichzeitig sehr leistungsfähige Mittel zur Darstellung von Daten sind Pivot-Tabellen. Sie können aus Daten erstellt werden, die enthalten sind in: a) einem Excel-Arbeitsblatt, b) einem OLAP-Cube oder c) einem Power Pivot-Datenmodell. In den letzten beiden Fällen können Sie zusätzlich zur Pivot-Tabelle analytische Funktionen (Cube-Funktionen) verwenden, um einen Bericht auf einem Excel-Blatt zu generieren. Pivot-Tabellen sind einfacher. Cube-Funktionen sind komplexer, bieten aber mehr Flexibilität, insbesondere bei der Berichterstellung, sodass sie in Dashboards weit verbreitet sind.

Die folgende Diskussion gilt für Cube-Formeln und Pivot-Tabellen basierend auf dem Power Pivot-Modell und in einigen Fällen basierend auf OLAP-Cubes.

Eine einfache Möglichkeit, die Funktionen von Würfeln zu erhalten

Als (falls) Sie anfingen, VBA-Code zu lernen, haben Sie gelernt, dass der einfachste Weg, den Code zu erhalten, darin besteht, ein Makro aufzuzeichnen. Außerdem kann der Code bearbeitet werden, es können Schleifen, Überprüfungen hinzugefügt werden usw. Ebenso ist der einfachste Weg, einen Satz von Würfelfunktionen zu erhalten, die Konvertierung der Pivot-Tabelle (Abb. 2). Stellen Sie sich auf eine beliebige Zelle der Pivot-Tabelle, gehen Sie zur Registerkarte Analyse, klicken Sie auf die Schaltfläche Mittel OLAP, und drücke In Formeln umwandeln.

Reis. 2. Konvertieren einer PivotTable in einen Cube-Funktionssatz

Die Zahlen werden gespeichert, und das sind keine Werte, sondern Formeln, die Daten aus dem Power Pivot-Datenmodell extrahieren (Abb. 3). Sie können die resultierende Tabelle formatieren. Insbesondere können Sie Zeilen und Spalten innerhalb der Tabelle löschen und einfügen. Der Slice bleibt bestehen und wirkt sich auf die Daten in der Tabelle aus. Wenn die Originaldaten aktualisiert werden, werden auch die Zahlen in der Tabelle aktualisiert.

Reis. 3. Tabelle basierend auf Würfelformeln

CUBEVALUE()-Funktion

Dies ist vielleicht die Hauptfunktion von Würfeln. Es entspricht der Fläche Werte Pivot-Tabelle. CUBEVALUE ruft Daten aus einem Power Pivot-Cube oder -Modell ab und zeigt sie außerhalb einer PivotTable an. Dadurch sind Sie nicht durch die Grenzen der Pivot-Tabelle eingeschränkt und können Reports mit unzähligen Möglichkeiten erstellen.

Eine Formel von Grund auf neu schreiben

Sie müssen die fertige PivotTable nicht konvertieren. Sie können jede Würfelformel von Grund auf neu schreiben. In Zelle C10 (Abb. 4) wird beispielsweise folgende Formel eingetragen:

Reis. 4. Die Funktion CUBEVALUE() in Zelle C10 gibt Fahrradverkäufe für alle Jahre zurück, wie in der Pivot-Tabelle

Kleiner Trick. Um das Lesen von Würfelformeln zu erleichtern, ist es wünschenswert, dass in jeder Zeile nur ein Argument steht. Sie können das Excel-Fenster verkleinern. Klicken Sie dazu auf das Symbol Rollen Sie in das Fenster befindet sich in der oberen rechten Ecke des Bildschirms. Passen Sie dann die Fenstergröße horizontal an. Alternative Möglichkeit– Erzwingen Sie, dass der Text der Formel in eine neue Zeile umbrochen wird. Setzen Sie dazu in der Bearbeitungsleiste den Cursor an die Stelle, an der Sie eine Überweisung vornehmen möchten, und drücken Sie Alt + Eingabetaste.

Reis. 5. Fenster minimieren

Syntax der Funktion CUBEVALUE()

Die Excel-Hilfe ist völlig korrekt und für Anfänger völlig nutzlos:

CUBEVALUE(Verbindung, [Element_Ausdruck1], [Element_Ausdruck2], ...)

Verbindung ist ein erforderliches Argument; eine Textzeichenfolge, die den Namen der Verbindung zum Cube darstellt.

elementausdruck– optionales Argument; eine Textzeichenfolge, die einen MDX darstellt, der ein Element oder Tupel im Cube zurückgibt. Außerdem kann „element_expression“ ein Satz sein, der mit der CUBESET-Funktion definiert wird. Verwenden Sie „member_expression“ als Slice, um den Teil des Cubes zu bestimmen, für den Sie einen aggregierten Wert zurückgeben möchten. Wenn in element_expression keine Kennzahl angegeben ist, wird die Standardkennzahl für diesen Cube verwendet.

Bevor Sie mit der Erläuterung der Syntax der Funktion WÜRFELWERT fortfahren, ein paar Worte zu Würfeln, Datenmodellen und dem Kryptischen Tupel.

Einige Hintergrundinformationen zu OLAP-Cubes und Power Pivot-Datenmodellen

OLAP-Datenwürfel ( Ö n l inne EIN analytisch P rocessing - Betriebsdatenanalyse) wurden speziell für die analytische Verarbeitung und schnelle Datenextraktion daraus entwickelt. Stellen Sie sich einen dreidimensionalen Raum vor, in dem die Achsen Zeiträume, Städte und Waren sind (Abb. 5a). Die Knoten eines solchen Koordinatengitters enthalten die Werte verschiedener Kennzahlen: Verkaufsvolumen, Gewinn, Kosten, Anzahl der verkauften Einheiten usw. Stellen Sie sich nun vor, dass es Dutzende von Messungen gibt, oder sogar Hunderte ... und es gibt auch eine viele Maßnahmen. Dies ist der mehrdimensionale OLAP-Würfel. Das Erstellen, Konfigurieren und Aktualisieren von OLAP-Cubes ist die Aufgabe von IT-Experten.

Reis. 5a. 3D-OLAP-Würfel

Excel-Analyseformeln (Würfelformeln) extrahieren Achsentitel (z. B. Zeit), Namen von Elementen auf diesen Achsen (August, September), Werte von Maßen am Schnittpunkt der Koordinaten. Es ist diese Struktur, die es würfelbasierten Pivot-Tabellen und Würfelformeln ermöglicht, so flexibel zu sein und sich an die Bedürfnisse der Benutzer anzupassen. Pivot-Tabellen basieren Excel-Tabellen verwenden keine Maßnahmen, daher sind sie für Datenanalysezwecke nicht so flexibel.

Power Pivot ist eine relativ neue Microsoft-Funktion. Dies ist eine integrierte Excel- und etwas unabhängige Umgebung mit einer vertrauten Benutzeroberfläche. Power Pivot ist Standard-PivotTables weit überlegen. Gleichzeitig ist die Entwicklung von Cubes in Power Pivot relativ einfach und erfordert vor allem nicht die Beteiligung eines IT-Spezialisten. Microsoft verwirklicht seinen Slogan: „Business Analytics – to the masses!“. Obwohl Power Pivot-Modelle nicht zu 100 % Cubes sind, können sie auch als Cubes bezeichnet werden (weitere Einzelheiten finden Sie im Power Pivot-Einführungskurs von Mark Moore und in der längeren Ausgabe von Rob Colley, Power Pivot DAX Formulas).

Die Hauptbestandteile eines Würfels sind Dimensionen, Hierarchien, Ebenen, Elemente (oder Elemente; auf Englisch Mitglieder) und Kennzahlen (Kennzahlen). Messung - das Hauptmerkmal der analysierten Daten. Zum Beispiel Produktkategorie, Zeitraum, Vertriebsregion. Eine Dimension ist etwas, das wir auf eine der Achsen der Pivot-Tabelle setzen können. Jede Dimension enthält neben eindeutigen Werten ein Element, das alle Elemente dieser Dimension aggregiert.

Die Messungen basieren auf Hierarchie. Beispielsweise kann eine Produktkategorie in Unterkategorien, dann in Modelle und schließlich in Produktnamen unterteilt werden (Abb. 5b) Die Hierarchie ermöglicht es Ihnen, zusammenfassende Daten zu erstellen und diese auf verschiedenen Strukturebenen zu analysieren. In unserem Beispiel die Hierarchie Kategorie beinhaltet 4 eben.

Elemente(einzelne Mitglieder) sind auf allen Ebenen präsent. Die Kategorieebene hat beispielsweise vier Elemente: Zubehör, Fahrräder, Kleidung, Komponenten. Andere Ebenen haben ihre eigenen Elemente.

Maße sind errechnete Werte, wie z. B. Verkaufsvolumen. Kennzahlen in Cubes werden in ihrer eigenen Dimension namens gespeichert (siehe Abbildung 9 unten). Kennzahlen haben keine Hierarchien. Jede Kennzahl berechnet und speichert einen Wert für alle Dimensionen und alle Mitglieder und schneidet basierend darauf, welche Dimensionsmitglieder wir auf der Achse platzieren. Sie sagen auch, welche Koordinaten wir setzen werden, oder welchen Filterkontext wir setzen werden. Zum Beispiel in Abb. 5a In jedem kleinen Würfel wird dieselbe Kennzahl berechnet - Gewinn. Und der von der Kennzahl zurückgegebene Wert hängt von den Koordinaten ab. Rechts in Abbildung 5a wird gezeigt, dass der Gewinn (in drei Koordinaten) für Moskau im Oktober für Äpfel = 63.000 Rubel beträgt. Die Messung kann als eine der Messungen interpretiert werden. Zum Beispiel in Abb. 5a anstelle einer Achse Produkte, Achse platzieren Mittel mit Elementen Umsatzvolumen, Profitieren, Einheiten verkauft. Dann wird jede Zelle einen Wert haben, zum Beispiel Moskau, September, Verkaufsvolumen.

Tupel- mehrere Elemente mit unterschiedlichen Abmessungen, die die Koordinaten entlang der Achsen des Würfels angeben, in dem wir das Maß berechnen. Zum Beispiel in Abb. 5a Tupel= Moskau, Oktober, Äpfel. Ebenfalls ein gültiges Tupel ist Perm, Äpfel. Ein anderer ist Äpfel, August. Dimensionen, die nicht im Tupel enthalten sind, sind implizit im Tupel vorhanden und werden durch das Standardelement dargestellt. Somit ist eine Zelle in einem mehrdimensionalen Raum immer durch einen vollständigen Satz von Koordinaten definiert, selbst wenn einige davon aus dem Tupel weggelassen werden. Sie können nicht zwei Elemente derselben Dimension in ein Tupel aufnehmen, die Syntax lässt dies nicht zu. Zum Beispiel das ungültige Tupel Moskau und Perm, Äpfel. Um einen solchen multidimensionalen Ausdruck zu implementieren, benötigen Sie einen Satz von zwei Tupeln: Moskau und Äpfel + Perm und Äpfel.

Satz von Elementen– mehrere Elemente der gleichen Dimension. Zum Beispiel Äpfel und Birnen. Satz von Tupeln- mehrere Tupel, die jeweils aus denselben Dimensionen in derselben Reihenfolge bestehen. Zum Beispiel ein Satz aus zwei Tupeln: Moskau, Äpfel und Perm, Bananen.

Autovervollständigung zur Rettung

Kehren wir zur Syntax der Funktion WÜRFELWERT zurück. Lassen Sie uns die automatische Vervollständigung verwenden. Beginnen Sie mit der Eingabe einer Formel in eine Zelle:

Excel schlägt alle in der Excel-Arbeitsmappe verfügbaren Verbindungen vor:

Reis. 6. Die Verbindung zum Power Pivot-Datenmodell heißt immer ThisWorkbookDataModel

Reis. 7. Verbindungen zu Würfeln

Fahren wir mit der Eingabe der Formel (in unserem Fall für das Datenmodell) fort:

Die automatische Vervollständigung schlägt alle verfügbaren Tabellen und Datenmodellmaße vor:

Reis. 8. Verfügbare Elemente der ersten Ebene - Tabellennamen und eine Reihe von Maßnahmen (hervorgehoben)

Wählen Sie ein Symbol aus Mittel. Zeigen auf:

CUBEVALUE(» DiesesArbeitsmappen-Datenmodell » ; » .

Autocomplete schlägt alle verfügbaren Maßnahmen vor:

Reis. 9. Verfügbare Elemente der zweiten Ebene im Maßnahmenpaket

Wählen Sie eine Maßnahme. Fügen Sie Anführungszeichen hinzu, schließen Sie die Klammer und drücken Sie die Eingabetaste.

CUBEVALUE(" DiesesArbeitsmappen-Datenmodell" ; " . ")

Reis. 10. Formel CUBEVALUE in einer Excel-Zelle

Auf ähnliche Weise können Sie der Formel ein drittes Argument hinzufügen:

VBA in Excel Excel.PivotTable-Objekt und Arbeiten mit PivotTables und OLAP-Cubes in Excel

10.8 Arbeiten mit Pivot-Tabellen (PivotTable-Objekt)

Excel.PivotTable-Objekt, programmgesteuertes Arbeiten mit PivotTables und OLAP-Cubes in Excel mithilfe von VBA, PivotCache-Objekt, Erstellen eines PivotTable-Layouts

Während des Betriebs der meisten Unternehmen werden die sogenannten Rohdaten über Aktivitäten gesammelt. Beispielsweise können für ein Handelsunternehmen Daten zum Warenverkauf gesammelt werden - für Unternehmen für jeden Einkauf separat zellulare Kommunikation- Statistik laden an Basisstationen usw. Sehr oft benötigt das Management eines Unternehmens analytische Informationen, die auf der Grundlage von Rohinformationen generiert werden - beispielsweise um den Beitrag jedes Produkttyps zum Einkommen des Unternehmens oder die Servicequalität in einem bestimmten Bereich zu berechnen Bahnhof. Es ist sehr schwierig, solche Informationen aus Rohinformationen zu extrahieren: Sie müssen sehr komplexe SQL-Abfragen ausführen, die lange dauern und häufig die laufende Arbeit stören. Daher werden immer mehr Rohdaten zuerst im Data Warehouse und dann in OLAP-Cubes hochgerollt, die für interaktive Analysen sehr praktisch sind. Am einfachsten kann man sich OLAP-Cubes als mehrdimensionale Tabellen vorstellen, bei denen statt der standardmäßigen zwei Dimensionen (Spalten und Zeilen, wie in normale Tische), kann es viele Messungen geben. Der Begriff "Schnitt" wird häufig verwendet, um Dimensionen in einem Würfel zu beschreiben. Beispielsweise benötigt eine Marketingabteilung möglicherweise Informationen nach Zeit, Region, Produkttyp, Vertriebskanal usw. Mithilfe von Cubes (im Gegensatz zu Standard-SQL-Abfragen) ist es sehr einfach, Antworten auf Fragen zu erhalten wie „Wie viele Produkte dieser Art wurden im vierten Quartal des letzten Jahres in der Region Nordwest durch regionale Distributoren verkauft.

Natürlich können Sie solche Cubes nicht in regulären Datenbanken erstellen. OLAP-Cubes erfordern spezialisierte Softwareprodukte. SQL Server wird mit einer OLAP-Datenbank von Microsoft namens Analysis Services geliefert. Es gibt OLAP-Lösungen von Oracle, IBM, Sybase usw.

Um mit solchen Cubes zu arbeiten, ist ein spezieller Client in Excel integriert. Auf Russisch heißt es Übersichtstabelle(Auf dem grafischen Bildschirm ist es über das Menü verfügbar Daten -> Übersichtstabelle) und auf Englisch - Pivot-Tabelle. Dementsprechend heißt das Objekt, das dieser Client darstellt, PivotTable. Zu beachten ist, dass nicht nur mit OLAP-Cubes, sondern auch mit regulären Daten in Excel-Tabellen oder Datenbanken gearbeitet werden kann, dabei aber viele Features verloren gehen.

Die PivotTable und das PivotTable-Objekt sind Softwareprodukte von Panorama Software, die von Microsoft erworben und in Excel integriert wurden. Daher unterscheidet sich die Arbeit mit dem PivotTable-Objekt etwas von der Arbeit mit anderen Excel-Objekten. Herauszufinden, was zu tun ist, ist oft schwierig. Daher wird empfohlen, den Makrorekorder aktiv zu nutzen, um Hinweise zu erhalten. Gleichzeitig müssen Benutzer bei der Arbeit mit Pivot-Tabellen häufig dieselben sich wiederholenden Vorgänge ausführen, sodass in vielen Situationen eine Automatisierung erforderlich ist.

Wie sieht es aus, programmgesteuert mit einer Pivot-Tabelle zu arbeiten?

Als Erstes müssen wir ein PivotCache-Objekt erstellen, das die aus der OLAP-Quelle abgerufenen Datensätze darstellt. Dieses PivotCache-Objekt kann sehr bedingt mit QueryTable verglichen werden. Pro PivotTable-Objekt kann nur ein PivotCache-Objekt verwendet werden. Das PivotCache-Objekt wird mit der Add()-Methode der PivotCaches-Sammlung erstellt:

Dim PC1 als PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches ist eine Standardsammlung, und von den Methoden, die eine eingehende Betrachtung verdienen, kann darin nur die Add()-Methode genannt werden. Diese Methode benötigt zwei Parameter:

• Quelle Typ- erforderlich, definiert den Typ der Datenquelle für die Pivot-Tabelle. Sie können eine PivotTable basierend auf einem Bereich in Excel, Daten aus einer Datenbank, einer externen Datenquelle, einer anderen PivotTable usw. erstellen. In der Praxis macht es meist nur Sinn, OLAP einzusetzen, wenn viele Daten vorhanden sind – dementsprechend wird ein spezialisierter externer Speicher benötigt (z. B. Microsoft Analysis Services). In diesem Fall wird xlExternal ausgewählt.
• Quelldaten- in allen Fällen erforderlich, außer wenn der Wert des ersten Parameters xlExternal ist. Genau genommen definiert es den Datenbereich, auf dessen Basis die PivotTable erstellt wird. Nimmt normalerweise ein Range-Objekt.

Die nächste Aufgabe besteht darin, die Parameter des PivotCache-Objekts zu konfigurieren. Wie bereits erwähnt, ist dieses Objekt QueryTable sehr ähnlich, und sein Satz von Eigenschaften und Methoden ist sehr ähnlich. Einige der meisten wichtige Eigenschaften und Methoden:

• ADOVerbindung- die Fähigkeit, ein ADO-Verbindungsobjekt zurückzugeben, das automatisch erstellt wird, um eine Verbindung zu einer externen Datenquelle herzustellen. Wird verwendet für zusätzliche Einstellungen Verbindungseigenschaften.
• Verbindung- funktioniert genauso wie die gleichnamige Objekteigenschaft QueryTable. Kann eine Verbindungszeichenfolge, ein vorgefertigtes Recordset-Objekt, Textdatei, Webanfrage. Microsoft-Datei Anfrage. Meistens wird beim Arbeiten mit OLAP direkt ein Verbindungsstring geschrieben (da es wenig Sinn macht, ein Recordset-Objekt zu erhalten, um beispielsweise Daten zu ändern – OLAP-Datenquellen sind fast immer nur lesbar). Das Festlegen dieser Eigenschaft zum Herstellen einer Verbindung mit der Foodmart-Datenbank (Analysis Services-Beispieldatenbank) auf dem LONDON-Server könnte beispielsweise so aussehen:

PC1.Connection = "OLEDB;Anbieter=MSOLAP.2;Datenquelle=LONDON1;Anfangskatalog = FoodMart 2000"

• Eigenschaften Befehlstyp und Befehlstext beschreiben die Art des Befehls, der an den Datenbankserver gesendet wird, und den Text des Befehls selbst auf die gleiche Weise. Um beispielsweise auf den Sales-Cube zuzugreifen und ihn vollständig auf dem Client zwischenzuspeichern, können Sie Code wie verwenden

• Eigentum LokaleVerbindung ermöglicht es Ihnen, eine Verbindung zu einem lokalen Cube (*.cub-Datei) herzustellen, der von Excel erstellt wurde. Natürlich wird dringend davon abgeraten, solche Dateien für die Arbeit mit "Produktions" -Datenmengen zu verwenden - nur zum Zwecke der Erstellung von Layouts usw.
• Eigentum MemoryUsed gibt die Menge an RAM zurück, die von PivotCache verwendet wird. Wenn die auf diesem PivotCache basierende PivotTable noch nicht erstellt und geöffnet wurde, wird 0 zurückgegeben. Kann verwendet werden, um zu überprüfen, ob Ihre Anwendung auf schwachen Clients funktioniert.
• Eigentum OLAP gibt True zurück, wenn der PivotCache mit dem OLAP-Server verbunden ist.
• OptimizeCache- die Fähigkeit, die Struktur des Caches zu optimieren. Das anfängliche Laden von Daten dauert länger, aber dann kann sich die Arbeitsgeschwindigkeit erhöhen. Für OLE DB-Quellen funktioniert das nicht.

Die übrigen Eigenschaften des PivotCache-Objekts sind mit denen des QueryTable-Objekts identisch und werden daher hier nicht erläutert.

Die Hauptmethode des PivotCache-Objekts ist die Methode CreatePivotTable(). Mit Hilfe dieser Methode wird der nächste Schritt durchgeführt - die Erstellung einer Pivot-Tabelle (PivotTable-Objekt). Diese Methode benötigt vier Parameter:

• TabelleZiel ist der einzige erforderliche Parameter. Akzeptiert ein Range-Objekt, in dessen oberer linker Ecke die Pivot-Tabelle platziert wird.
• Tabellenname- Name der Pivot-Tabelle. Wenn nicht angegeben, wird automatisch der Name des Formulars "PivotTable1" generiert.
• Daten lesen- Wenn auf True gesetzt, wird der gesamte Inhalt des Cubes automatisch zwischengespeichert. Sie müssen mit diesem Parameter sehr vorsichtig umgehen, da seine falsche Verwendung die Last auf dem Client dramatisch erhöhen kann.
• DefaultVersion- Diese Eigenschaft wird normalerweise nicht angegeben. Ermöglicht es Ihnen, die Version der zu erstellenden PivotTable anzugeben. Standardmäßig wird die neueste Version verwendet.

Das Erstellen einer Pivot-Tabelle in der ersten Zelle des ersten Blatts der Arbeitsmappe könnte folgendermaßen aussehen:

PC1.CreatePivotTable-Bereich ("A1")

Die Pivot-Tabelle wurde erstellt, ist aber unmittelbar nach der Erstellung leer. Es bietet vier Bereiche, in denen Sie Felder aus der Quelle platzieren können (auf dem Grafikbildschirm kann dies alles entweder über das Fenster konfiguriert werden Feldliste der Pivot-Tabelle- es öffnet sich automatisch oder per Knopfdruck Layout auf dem letzten Bildschirm des PivotTable-Assistenten):

• Spaltenbereich- es enthält jene Dimensionen („Abschnitt“, in dem die Daten analysiert werden), deren Mitglieder weniger sind;
• Linienbereich- jene Dimensionen, deren Mitglieder mehr sind;
• Seitenbereich- diejenigen Messungen, nach denen nur gefiltert werden muss (z. B. um Daten nur für diese und jene Region oder nur für dieses und jenes Jahr anzuzeigen);
• Datenbereich- eigentlich der zentrale Teil des Tisches. Diese numerischen Daten (z. B. die Höhe der Verkäufe), die wir analysieren.

Es ist schwierig, sich darauf zu verlassen, dass der Benutzer Elemente in allen vier Bereichen korrekt platziert. Außerdem kann es dauern bestimmte Zeit. Daher ist es häufig erforderlich, Daten programmgesteuert in einer PivotTable anzuordnen. Diese Operation wird mit dem CubeField-Objekt ausgeführt. Die Haupteigenschaft dieses Objekts ist Orientierung, sie bestimmt, wo sich dieses oder jenes Feld befindet. Lassen Sie uns beispielsweise die Dimension Customers in den Spaltenbereich einfügen:

PT1.CubeFields("").Orientation = xlColumnField

Dann - die Zeitdimension zum Bereich der Saiten:

PT1.CubeFields("").Orientation = xlRowField

Dann - die Produktdimension zum Seitenbereich:

PT1.CubeFields("").Orientation = xlPageField

Und schließlich der Indikator (numerische Daten zur Analyse) Unit Sales:

PT1.CubeFields(".").Orientation = xlDataField

Erstaunlich nah...

Im Laufe der Arbeit musste ich oft komplexe Berichte erstellen, ich habe immer versucht, Gemeinsamkeiten in ihnen zu finden, um sie einfacher und universeller zu verfassen, ich habe sogar einen Artikel zu diesem Thema „Der Osipov-Baum“ geschrieben und veröffentlicht. Sie kritisierten jedoch meinen Artikel und sagten, dass alle von mir angesprochenen Probleme längst in MOLAP.RU v.2.4 (www.molap.rgtu.ru) gelöst seien, und empfahlen, sich die Pivot-Tabellen in EXCEL anzusehen.
Es stellte sich als so einfach heraus, dass ich, nachdem ich meine genialen kleinen Hände daran befestigt hatte, ein sehr bekam einfache Schaltung zum Entladen von Daten aus 1C7 oder jeder anderen Datenbank (im Folgenden bedeutet 1C jede Datenbank) und Analyse in OLAP.
Ich denke, viele OLAP-Upload-Schemata sind zu kompliziert, ich wähle Einfachheit.

Eigenschaften :

1. Zum Arbeiten wird nur EXCEL 2000 benötigt.
2. Der Benutzer kann Berichte ohne Programmierung selbst gestalten.
3. Hochladen von 1C7 in einem einfachen Textdateiformat.
4. Für Buchhaltungsbuchungen gibt es bereits eine universelle Verarbeitung zum Entladen, die in jeder Konfiguration funktioniert. Für das Entladen anderer Daten gibt es Musterverarbeitungen.
5. Sie können Berichtsformulare vorentwerfen und sie dann auf verschiedene Daten anwenden, ohne sie neu zu entwerfen.
6. Hübsch gute Leistung. In der ersten langen Phase werden die Daten zunächst aus einer Textdatei in EXCEL importiert und ein OLAP-Würfel erstellt, und dann kann auf der Grundlage dieses Würfels recht schnell ein beliebiger Bericht erstellt werden. Beispielsweise werden Daten zum Warenverkauf in einem Geschäft für 3 Monate mit einem Sortiment von 6000 Waren in 8 Minuten auf Cel600-128M in EXCEL geladen, die Bewertung nach Waren und Gruppen (OLAP-Bericht) wird in 1 Minute neu berechnet.
7. Die Daten werden von 1C7 vollständig für den angegebenen Zeitraum heruntergeladen (alle Bewegungen, für alle Lager, Firmen, Konten). Beim Import in EXCEL können Filter verwendet werden, die nur die notwendigen Daten zur Analyse laden (z. B. aus allen Bewegungen nur Verkäufe).
8. Derzeit wurden Methoden entwickelt, um Bewegungen oder Rückstände zu analysieren, jedoch keine Bewegungen und Rückstände zusammen, obwohl dies grundsätzlich möglich ist.

Was ist OLAP : (www.molap.rgtu.ru)

Angenommen, Sie haben ein Handelsnetzwerk. Lassen Sie die Daten zu Handelsoperationen in eine Textdatei oder eine Tabelle der Form hochladen:

Datum - Transaktionsdatum
Monat - Betriebsmonat
Woche - Betriebswoche
Typ - Kauf, Verkauf, Rückgabe, Abschreibung
Kontrahent - eine externe Organisation, die an der Operation teilnimmt
Autor - die Person, die die Rechnung ausgestellt hat

In 1C entspricht beispielsweise eine Zeile dieser Tabelle einer Zeile der Rechnung, einige Felder (Auftragnehmer, Datum) werden aus dem Rechnungskopf übernommen.

Daten zur Analyse werden in der Regel für einen bestimmten Zeitraum in das OLAP-System hochgeladen, von dem grundsätzlich durch Lastfilter ein anderer Zeitraum abgegrenzt werden kann.

Diese Tabelle ist die Quelle für die OLAP-Analyse.

Bericht	Messungen	Daten	Filter
Wie viele Waren und für welchen Betrag werden pro Tag verkauft?	Datum, Produkt	Gesamtmenge	Ansicht="verkauf"
Welche Kontrahenten haben welche Waren für welchen Betrag pro Monat geliefert?	Monat, Auftragnehmer, Produkt	Summe	View="Kauf"
In welcher Höhe haben die Betreiber von Rechnungen welcher Art für den gesamten Berichtszeitraum ausgestellt?		Summe

Der Benutzer bestimmt selbst, welche Felder der Tabelle Dimensionen sein sollen, welche Daten und welche Filter angewendet werden sollen. Das System selbst erstellt einen Bericht in visuell tabellarischer Form. Dimensionen können in den Zeilen- oder Spaltenüberschriften einer Berichtstabelle platziert werden.
Wie Sie sehen können, können Sie aus einer einfachen Tabelle viele Daten in Form verschiedener Berichte erhalten.

So verwenden Sie es selbst :

Entpacken Sie die Daten aus dem Distributionskit genau in das Verzeichnis c:\fixin (z Handelssystem möglicherweise in c:\reports) . Lesen Sie readme.txt und befolgen Sie alle darin enthaltenen Anweisungen.

Zuerst müssen Sie eine Verarbeitung schreiben, die Daten von 1C in eine Textdatei (Tabelle) hochlädt. Sie müssen die Zusammensetzung der Felder definieren, die hochgeladen werden.
Beispielsweise entlädt eine vorgefertigte universelle Verarbeitung, die in jeder Konfiguration funktioniert und Buchungen für einen Zeitraum für die OLAP-Analyse entlädt, die folgenden Felder zur Analyse:

Datum|Wochentag|Woche|Jahr|Quartal|Monat|Dokument|Firma|Belastung|DtNomenklatur
|DtGroupNomenclature|DtSectionNomenclature|Credit|Amount|ValAmount|Menge
|Währung|DtContractors|DtGroupContractors|KtContractors|KtGroupContractors|
CTMiscellaneousObjects

Wo unter den Präfixen Dt (Kt) Subcontos von Debit (Credit) vorhanden sind, ist Group eine Gruppe dieses Subcontos (falls vorhanden), Section ist eine Gruppe einer Gruppe, Class ist eine Section-Gruppe.

Für ein Handelssystem können die Felder wie folgt aussehen:

Richtung|Art der Bewegung|Barzahlung|Produkt|Menge|Preis|Betrag|Datum|Firma
|Lager|Währung|Dokument|Wochentag|Woche|Jahr|Quartal|Monat|Autor
|Produktkategorie|Bewegungskategorie|Kontrahentenkategorie|Produktgruppe
|ValAmount|Einstandspreis|Auftragnehmer

Für die Datenanalyse werden Tabellen "Analyse der Bewegungen.xls" ("Analyse der Buchhaltung.xls") verwendet. Deaktivieren Sie beim Öffnen keine Makros, da Sie sonst keine Berichte aktualisieren können (sie werden durch Makros in der VBA-Sprache ausgelöst). Diese Dateien nehmen ihre initialen Daten aus den Dateien C:\fixin\motions.txt (C:\fixin\buh.txt), ansonsten sind sie gleich. Daher müssen Sie Ihre Daten möglicherweise in eine dieser Dateien kopieren.
Damit Ihre Daten in EXCEL geladen werden, wählen oder schreiben Sie Ihren eigenen Filter und klicken Sie auf der Seite "Bedingungen" auf die Schaltfläche "Generieren".
Berichtsblätter beginnen mit dem Präfix „Von“. Gehen Sie zum Berichtsblatt, klicken Sie auf „Aktualisieren“ und die Berichtsdaten werden entsprechend den zuletzt geladenen Daten geändert.
Wenn Sie mit den Standardberichten nicht zufrieden sind, gibt es ein Blatt OtchTemplate. Kopieren Sie es auf ein neues Blatt und passen Sie die Berichtsansicht an, indem Sie auf diesem Blatt mit einer Pivot-Tabelle arbeiten (mehr über das Arbeiten mit Pivot-Tabellen - in jedem Buch über EXCEL 2000). Ich empfehle, Berichte auf einem kleinen Datensatz einzurichten und sie dann auf einem großen Array auszuführen, weil Es gibt keine Möglichkeit, das Neuzeichnen der Tabelle jedes Mal zu deaktivieren, wenn sich das Berichtslayout ändert.

Technische Hinweise :

Beim Hochladen von Daten von 1C wählt der Benutzer den Ordner aus, in den die Datei hochgeladen werden soll. Ich habe dies getan, weil es sehr wahrscheinlich ist, dass in naher Zukunft mehrere Dateien (Überreste und Bewegungen) hochgeladen werden. Anschließend werden durch Drücken der Schaltfläche "Senden" --> "Zur OLAP-Analyse in EXCEL 2000" im Explorer die Daten aus dem ausgewählten Ordner in den Ordner C:\fixin kopiert. (Damit dieser Befehl in der Liste des "Senden"-Befehls erscheint, müssen Sie die Datei "Für OLAP-Analyse in EXCEL 2000.bat" in das Verzeichnis C:\Windows\SendTo kopieren) Laden Sie daher die Daten sofort unter Angabe von Namen hoch zu den Dateien motions.txt oder buh.txt.

Textdateiformat:
Die erste Zeile der Textdatei enthält die durch „|“ getrennten Spaltenüberschriften, die restlichen Zeilen enthalten die durch „|“ getrennten Werte dieser Spalten.

Microsoft Query wird verwendet, um Textdateien in Excel zu importieren ( Komponente EXCEL) für den Betrieb ist eine shema.ini-Datei im Importverzeichnis (C:\fixin) erforderlich, die folgende Informationen enthält:

ColNameHeader=Wahr
Format=getrennt(|)
MaxScanRows=3
Zeichensatz=ANSI
ColNameHeader=Wahr
Format=getrennt(|)
MaxScanRows=3
Zeichensatz=ANSI

Erläuterung: motions.txt und buh.txt ist der Name des Abschnitts, entspricht dem Namen der importierten Datei, beschreibt, wie eine Textdatei in Excel importiert wird. Die restlichen Parameter bedeuten, dass die erste Zeile die Namen der Spalten enthält, das Spaltentrennzeichen "|" ist, der Zeichensatz Windows ANSI (für DOS - OEM) ist.
Der Feldtyp wird anhand der in der Spalte enthaltenen Daten (Datum, Zahl, Zeichenfolge) automatisch ermittelt.
Die Liste der Felder muss nirgends beschrieben werden - EXCEL und OLAP bestimmen anhand der Überschriften in der ersten Zeile selbst, welche Felder in der Datei enthalten sind.

Achtung, überprüfen Sie Ihre regionalen Einstellungen "Systemsteuerung" --> "Regionale Einstellungen". In meiner Verarbeitung werden die Zahlen mit einem Komma-Trennzeichen hochgeladen, und die Daten haben das Format "TT.MM.JJJJ".

Wenn Sie auf die Schaltfläche „Generieren“ klicken, werden die Daten in die Pivot-Tabelle auf dem Blatt „Basis“ geladen, und alle Berichte auf den Blättern „Rückgabe“ übernehmen Daten aus dieser Pivot-Tabelle.

Ich verstehe, dass Liebhaber von MS SQL Server und leistungsstarken Datenbanken anfangen werden zu meckern, dass mir alles zu vereinfacht ist, dass meine Verarbeitung auf einer jährlichen Stichprobe sterben wird, aber zuallererst möchte ich mittelständischen Organisationen die Vorteile der OLAP-Analyse bieten . Ich würde dieses Produkt als jährliches Analysetool für Großhändler, vierteljährliche Analyse für Einzelhändler und operative Analyse für jede Organisation positionieren.

Ich musste mit VBA herumbasteln, damit die Daten aus einer Datei mit beliebiger Liste von Feldern genommen wurden und es möglich war, Berichtsformulare im Voraus vorzubereiten.

Beschreibung der Arbeit in EXCEL (für Benutzer):

Hinweise zur Verwendung von Berichten:
1. Senden Sie die heruntergeladenen Daten zur Analyse (erkundigen Sie sich beim Administrator). Klicken Sie dazu mit der rechten Maustaste auf den Ordner, in den Sie Daten aus 1C hochgeladen haben, und wählen Sie den Befehl "Senden", dann "Zur OLAP-Analyse in EXCEL 2000".
2. Öffnen Sie die Datei „Bewegungsanalyse.xls“.
3. Wählen Sie den Filterwert aus, die benötigten Filter können auf der Registerkarte "Werte" hinzugefügt werden.
4. Klicken Sie auf die Schaltfläche "Generieren", und die heruntergeladenen Daten werden in EXCEL geladen.
5. Nachdem Sie die Daten in EXCEL geladen haben, können Sie verschiedene Berichte anzeigen. Klicken Sie dazu im ausgewählten Bericht einfach auf die Schaltfläche „Aktualisieren“. Berichtsblätter beginnen mit Rep.
Aufmerksamkeit! Nachdem Sie den Filterwert geändert haben, müssen Sie erneut auf die Schaltfläche "Generieren" klicken, damit die Daten in EXCEL gemäß den Filtern aus der Upload-Datei neu geladen werden.

Verarbeitung aus der Demo:

Verarbeitung motionsbuh2011.ert ist die neueste Version von Entladevorgängen aus Accounting 7.7 für die Analyse in Excel. Es verfügt über das Kontrollkästchen „An Datei anhängen“, mit dem Sie Daten in Teilen nach Punkten hochladen, sie an dieselbe Datei anhängen und nicht erneut in dieselbe Datei hochladen können:

Verarbeitung motionswork.ert lädt Verkaufsdaten zur Analyse in Excel hoch.

Beispiele melden:

Schach per Post:

Arbeitsbelastung der Bediener nach Rechnungsarten:

P.S. :

Es ist klar, dass Sie nach einem ähnlichen Schema das Entladen von Daten aus 1C8 organisieren können.
Im Jahr 2011 wurde ich von einem Benutzer kontaktiert, der diese Verarbeitung in 1C7 abschließen musste, damit große Datenmengen hochgeladen werden konnten. Ich fand einen Outsourcer und erledigte diesen Job. Die Entwicklung ist also durchaus relevant.

Die Verarbeitung von Motionsbuh2011.ert wurde verbessert, um das Hochladen großer Datenmengen zu bewältigen.

Wählen Sie ein Dokument aus dem Archiv aus, um es anzuzeigen:

18,5 KB autos.xls

14 KB Länder.xls

Excel pr.r. 1.docx

Bibliothek
Materialien

Praktische Arbeit 1

"Der Zweck und die Schnittstelle von MS Excel"

Wenn Sie dieses Thema abschließen, werden Sie:

1. Erfahren Sie, wie Sie Tabellenkalkulationen ausführen;

2. Festlegen der Grundkonzepte: Zelle, Zeile, Spalte, Zellenadresse;

3. Erfahren Sie, wie Sie Daten in eine Zelle eingeben und die Bearbeitungsleiste bearbeiten.

5. So selektieren Sie ganze Zeilen, eine Spalte, mehrere nebeneinander liegende Zellen und die gesamte Tabelle.

Übung: Machen Sie sich mit den grundlegenden Elementen des MS Excel-Fensters vertraut.

Laufen Microsoft-Programm Excel. Schauen Sie sich das Programmfenster genau an.

Dokumente erstellt mitAUSGEZEICHNET , werden genanntArbeitsbücher und habe die Erweiterung. XLS. Neu Arbeitsmappe hat drei Arbeitsblätter namens BLATT1, BLATT2 und BLATT3. Diese Namen werden auf den Registerkarten unten auf dem Bildschirm aufgelistet. Um zu einem anderen Blatt zu wechseln, klicken Sie auf den Namen dieses Blattes.

Arbeitsblattaktionen:

Umbenennen eines Arbeitsblatts. Platzieren Sie den Mauszeiger auf dem Buchrücken des Arbeitsblatts und doppelklicken Sie mit der linken Maustaste oder rufen Sie auf Kontextmenü und wählen Sie Umbenennen.Benennen Sie das Blatt "WORKOUT"

Fügen Sie ein Arbeitsblatt ein . Wählen Sie den Blattreiter „Blatt 2“, vor dem Sie ein neues Blatt einfügen möchten, und über das KontextmenüFügen Sie ein neues Blatt ein und nennen Sie es "Muster". .

Löschen eines Arbeitsblatts. Wählen Sie den Blattreiter „Blatt 2“ aus und verwenden Sie das Kontextmenülöschen .

Zellen und Zellbereiche.

Der Arbeitsbereich besteht aus Zeilen und Spalten. Die Zeilen sind von 1 bis 65536 nummeriert. Die Spalten werden mit lateinischen Buchstaben bezeichnet: A, B, C, ..., AA, AB, ..., IV, insgesamt - 256. Am Schnittpunkt einer Zeile befindet sich eine Zelle und eine Säule. Jede Zelle hat ihre eigene Adresse: den Namen der Spalte und die Nummer der Zeile, an deren Schnittpunkt sie sich befindet. Zum Beispiel A1, CB234, P55.

Um mit mehreren Zellen zu arbeiten, ist es praktisch, sie zu "Bereichen" zusammenzufassen.

Ein Bereich sind Zellen, die in einem Rechteck angeordnet sind. Zum Beispiel A3, A4, A5, B3, B4, B5. Um einen Bereich zu schreiben, verwenden Sie ": »: A3:B5

8:20 - alle Zellen in den Zeilen 8 bis 20.

A:A - alle Zellen in Spalte A.

N:R - alle Zellen in Spalten von H bis R.

Die Zelladresse kann den Arbeitsblattnamen enthalten: Sheet8!A3:B6.

2. Wählen Sie Zellen in Excel aus

Was wir hervorheben

Aktionen

eine Zelle

Klicken Sie darauf oder verschieben Sie die Auswahl mit den Pfeiltasten.

Schnur

Klicken Sie auf eine Zeilennummer.

Spalte

Klicken auf einen Spaltennamen.

Zellreichweite

Ziehen Sie den Mauszeiger von der linken oberen Ecke des Bereichs nach rechts unten.

Mehrere Bereiche

Wählen Sie den ersten aus, drücken Sie SHIFT + F 8, wählen Sie den nächsten aus.

Ganzer Tisch

Klicken auf die Schaltfläche "Alle auswählen" (leere Schaltfläche links neben den Spaltennamen)

Sie können die Spaltenbreite und Zeilenhöhe ändern, indem Sie die Grenzen zwischen ihnen ziehen.

Verwenden Sie die Bildlaufleisten, um festzustellen, wie viele Zeilen die Tabelle hat und wie die letzte Spalte heißt.
Aufmerksamkeit!!! Um das Tabellenende schnell horizontal oder vertikal zu erreichen, müssen Sie die Tastenkombinationen drücken: Strg+→ - Spaltenende oder Strg+↓ - Zeilenende. Schnelle Rückkehr zum Anfang der Tabelle - Strg+Pos1.

Geben Sie in Zelle A3 die Adresse der letzten Spalte der Tabelle ein.

Wie viele Zeilen hat die Tabelle? Geben Sie die Adresse der letzten Zeile in Zelle B3 ein.

3. In EXCEL können Sie folgende Arten von Daten eingeben:

Zahlen.

Text (z. B. Überschriften und erläuterndes Material).

Funktionen (z. B. Summe, Sinus, Wurzel).

Formeln.

Daten werden in Zellen eingegeben. Um Daten einzugeben, muss die gewünschte Zelle ausgewählt werden. Es gibt zwei Möglichkeiten, Daten einzugeben:

Klicken Sie einfach in eine Zelle und geben Sie die gewünschten Daten ein.

Klicken Sie in die Zelle und in die Bearbeitungsleiste und geben Sie Daten in die Bearbeitungsleiste ein.

Drücken Sie Enter.

Geben Sie Ihren Namen in Zelle N35 ein, zentrieren Sie ihn in der Zelle und wenden Sie ihn fett an.
Geben Sie das aktuelle Jahr in Zelle C5 mithilfe der Bearbeitungsleiste ein.

4. Daten ändern.

Wählen Sie eine Zelle aus und drücken Sie F 2 und ändern Sie die Daten.

Markieren Sie die Zelle e klicken Sie in die Bearbeitungsleiste und ändern Sie dort die Daten.

Um die Formeln zu ändern, können Sie nur die zweite Methode verwenden.

Ändern Sie die Daten in einer Zelle N35, füge deinen Nachnamen hinzu. mit einer der Methoden.

5. Formeln eingeben.

Eine Formel ist ein arithmetisches Oder Boolescher Ausdruck auf denen Berechnungen in der Tabelle durchgeführt werden. Formeln bestehen aus Zellbezügen, Operationszeichen und Funktionen. Ms EXCEL hat einen sehr großen Satz eingebauter Funktionen. Mit ihrer Hilfe können Sie die Summe oder das arithmetische Mittel der Werte aus einem bestimmten Bereich von Zellen berechnen, Zinsen auf Einlagen berechnen usw.

Formeln beginnen immer mit einem Gleichheitszeichen. Nach Eingabe der Formel in die entsprechende Zelle erscheint das Ergebnis der Berechnung und die Formel selbst ist in der Bearbeitungsleiste zu sehen.

Aktion

Beispiele

+

Zusatz

A1+B1

-

Subtraktion

A1 - B2

*

Multiplikation

B3*C12

/

Aufteilung

A1 / B5

Potenzierung

A4 ^3

=, <,>,<=,>=,<>

Beziehungszeichen

A2

Sie können Klammern in Formeln verwenden, um die Reihenfolge der Aktionen zu ändern.

Automatische Vervollständigung.

Ein sehr praktisches Werkzeug, das nur in MS EXCEL verwendet wird, ist die automatische Vervollständigung benachbarter Zellen. Beispielsweise müssen Sie die Namen der Monate des Jahres in eine Spalte oder Zeile eingeben. Dies kann manuell erfolgen. Aber es gibt noch viel mehr bequeme Weise:

Geben Sie in der ersten Zelle den gewünschten Monat ein, zum Beispiel Januar.

Wählen Sie diese Zelle aus. In der unteren rechten Ecke des Auswahlrahmens befindet sich ein kleines Quadrat – der Füllgriff.

Bewegen Sie den Mauszeiger über den Füllgriff (er wird zu einem Kreuz), während Sie ihn gedrückt halten linker Knopf Maus, ziehen Sie die Markierung in die gewünschte Richtung. In diesem Fall wird der aktuelle Wert der Zelle neben dem Rahmen angezeigt.

Wenn Sie eine Zahlenreihe ausfüllen müssen, müssen Sie die ersten beiden Zahlen in die angrenzenden zwei Zellen eingeben (z. B. 1 in A4 und 2 in B4 eingeben), diese beiden Zellen auswählen und den Auswahlbereich durch ziehen Marker auf die gewünschte Größe.

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 2.docx

Bibliothek
Materialien

Praktische Arbeit 2

"Eingabe von Daten und Formeln in MS Excel-Tabellenzellen"

· Geben Sie Daten in Zellen ein Anderer Typ: Text, Zahlen, Formeln.

Übung: Führen Sie die erforderliche Dateneingabe und einfache Berechnungen in der Tabelle durch.

Technologie zur Aufgabenausführung:

1. Führen Sie das Programm ausMicrosoft Excel.

2. Zur ZelleA1 Blatt 2 Geben Sie den Text ein: "Gründungsjahr der Schule". Korrigieren Sie die Daten in der Zelle auf eine Ihnen bekannte Weise.

3. Zur ZelleIN 1 Geben Sie die Nummer ein - das Gründungsjahr der Schule (1971).

4. Zur ZelleC1 Geben Sie eine Zahl ein - das aktuelle Jahr (2016).

Aufmerksamkeit! Bitte beachten Sie, dass in MS Excel-Text Daten sind linksbündig, Zahlen und Datumsangaben rechtsbündig.

5. Markieren Sie eine ZelleD1 , verwenden Sie die Tastatur, um die Formel zur Berechnung des Alters der Schule einzugeben:=C1-B1

Aufmerksamkeit! Formeln beginnen immer mit einem Gleichheitszeichen«=». Zelladressen müssen in lateinischen Buchstaben ohne Leerzeichen eingegeben werden. Zellenadressen können in Formeln ohne Verwendung der Tastatur eingegeben werden, sondern einfach durch Anklicken der entsprechenden Zellen mit der Maus.

6. Löschen Sie den Inhalt einer ZelleD1 und geben Sie die Formel mit der Maus erneut ein. In einer ZelleD1 Zeichen installieren«=» , dann klicken Sie auf die ZelleC1, Beachten Sie, dass die Adresse dieser Zelle in angezeigt wurdeD1, ein Zeichen setzen«–» und klicken Sie auf die ZelleB1 , klicken(Eintreten).

7. Zur ZelleA2 Text eingeben"Mein Alter".

8. Zur ZelleB2 geben Sie Ihr Geburtsjahr ein.

9. Zur ZelleC2 Geben Sie das aktuelle Jahr ein.

10. Zelle eingebenD2 Formel zur Berechnung Ihres Alters im laufenden Jahr(=C2-B2).

11. Markieren Sie eine ZelleC2. Geben Sie die Nummer des nächsten Jahres ein. Beachten Sie die Neuberechnung in der ZelleD2 geschah automatisch.

12. Bestimmen Sie Ihr Alter im Jahr 2025. Ersetzen Sie dazu das Jahr in der ZelleC2 auf der2025.

Selbstständige Arbeit

Eine Übung: Berechnen Sie mit ET, ob 130 Rubel ausreichen, um alle Produkte zu kaufen, die Ihre Mutter für Sie bestellt hat, und ob es ausreicht, Chips für 25 Rubel zu kaufen?

Übungstechnik:
o Geben Sie in Zelle A1 „Nein“ ein.
o Geben Sie in den Zellen A2, A3 „1“, „2“ ein, wählen Sie die Zellen A2, A3 aus, zeigen Sie auf die untere rechte Ecke (ein schwarzes Kreuz sollte erscheinen), strecken Sie sie bis zur Zelle A6
o Geben Sie in Zelle B1 „Name“ ein
o Geben Sie in Zelle C1 „Preis in Rubel“ ein
o Geben Sie in Zelle D1 „Menge“ ein
o Geben Sie in Zelle E1 „Kosten“ usw. ein.
o In der Spalte „Kosten“ sind alle Formeln eingetragen Englische Sprache!
o In Formeln werden statt Variablen Zellnamen geschrieben.
o Nach dem Drücken der Eingabetaste anstelle der Formel erscheint sofort eine Zahl - das Ergebnis der Berechnung

o Berechnen Sie die Summe selbst.

Zeige dem Lehrer das Ergebnis!

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 3.docx

Bibliothek
Materialien

Praktische Arbeit 3

MS-Excel. Erstellen und Bearbeiten eines Tabellenkalkulationsdokuments»

Wenn Sie dieses Thema bearbeiten, lernen Sie:

 Erstellen und füllen Sie eine Tabelle mit Daten;

 Daten in einer Zelle formatieren und bearbeiten;

 Verwenden Sie einfache Formeln in der Tabelle;

 Formeln kopieren.

Übung:

1. Erstellen Sie eine Tabelle mit dem Zugfahrplan vom Bahnhof Saratov zum Bahnhof Samara. Die Gesamtansicht der Tabelle „Schedule“ ist in der Abbildung dargestellt.

2. Zelle auswählenA3 , ändern Sie das Wort "Golden" in "Great" und drücken Sie die TasteEintreten .

3. Zelle auswählenA6 , klicken Sie zweimal mit der linken Maustaste darauf und ersetzen Sie "Gloomy" durch "Veselkovo"

4. Zelle auswählenA5 Gehen Sie zur Bearbeitungsleiste und ersetzen Sie "Sennaya" durch "Sennaya 1".

5. Ergänzen Sie die Tabelle "Fahrplan" mit Berechnungen der Zeit der Zughalte in jeder Siedlung. (Spalten einfügen) Berechnen Sie die Gesamthaltezeit, die Gesamtfahrzeit, die Zeit, die der Zug benötigt, um von einem Ort zum anderen zu fahren.

Technologie zur Aufgabenausführung:

1. Verschieben Sie die Spalte Abfahrtszeit von Spalte C in Spalte D. Gehen Sie dazu folgendermaßen vor:

Wählen Sie Block C1:C7; eine Mannschaft auswählenAusgeschnitten .
Platzieren Sie den Cursor in Zelle D1;
Führen Sie den Befehl ausEinfügung ;
Richten Sie die Spaltenbreite so aus, dass sie der Kopfzeilengröße entspricht.;

2. Geben Sie in Zelle C1 den Text „Parken“ ein. Richten Sie die Spaltenbreite an der Kopfzeilengröße aus.

3. Erstellen Sie eine Formel, die die Parkzeit in einem Ort berechnet.

4. Sie müssen die Formel mit dem Füllgriff in den Block C4:C7 kopieren. Gehen Sie dazu folgendermaßen vor:
Um die aktive Zelle herum befindet sich ein Rahmen, in dessen Ecke sich ein kleines Rechteck befindet. Wenn Sie es greifen, erweitern Sie die Formel bis zur Zelle C7.

5. Geben Sie in Zelle E1 den Text „Fahrzeit“ ein. Richten Sie die Spaltenbreite an der Kopfzeilengröße aus.

6. Erstellen Sie eine Formel, die die Zeit berechnet, die ein Zug benötigt, um von einem Ort zum anderen zu fahren.

7. Ändern Sie das Zahlenformat für die Blöcke C2:C9 und E2:E9. Gehen Sie dazu folgendermaßen vor:

Markieren Sie den Zellenblock C2:C9;
Home - Format - Andere Zahlenformate - Uhrzeit und eingestellte Parameter (Stunden:Minuten) .

drücken Sie die TasteOK .

8. Berechnen Sie die Gesamtparkzeit.
Zelle C9 auswählen;
Drück den KnopfAutoSumme auf der Symbolleiste;
Bestätigen Sie die Auswahl des Zellenblocks C3:C8 und drücken Sie die TasteEintreten .

9. Geben Sie Text in Zelle B9 ein. Gehen Sie dazu folgendermaßen vor:

Zelle B9 auswählen;
Geben Sie den Text „Gesamtparkzeit“ ein. Richten Sie die Spaltenbreite an der Kopfzeilengröße aus.

10. Löschen Sie den Inhalt der Zelle C3.

Zelle C3 auswählen;
Führen Sie den Hauptmenübefehl aus Bearbeiten - Löschen oder klickenLöschen auf Tastatur;
Aufmerksamkeit! Der Computer berechnet den Betrag in Zelle C9 automatisch neu!!!

Führen Sie den Befehl aus Absagen oder klicken Sie auf die entsprechende Schaltfläche in der Symbolleiste.

11. Geben Sie in Zelle D9 den Text „Gesamtfahrzeit“ ein.

12. Berechnen Sie die Gesamtfahrzeit.

13. Färben Sie die Tabelle und markieren Sie die Ränder der Tabelle.

Selbstständige Arbeit

Rechnen Sie mit einer TabellenkalkulationExcelAusgaben von Schulkindern, die einen Ausflug in eine andere Stadt machen.

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 4.docx

Bibliothek
Materialien

Praktische Arbeit 4

"Links. Eingebaute Funktionen von MS Excel".

Wenn Sie dieses Thema bearbeiten, lernen Sie:

Führen Sie Kopier-, Verschiebe- und automatische Vervollständigungsvorgänge durch einzelne Zellen und Reichweiten.

Unterscheiden Sie zwischen Arten von Verknüpfungen (absolut, relativ, gemischt)

Verwenden Sie die integrierte mathematische und statistische Excel-Funktionen.

MS Excel enthält 320 eingebaute Funktionen. Der einfachste Weg Empfang alle Informationenüber jeden von ihnen ist das Menü zu verwendenBezug . Der Einfachheit halber sind Funktionen in Excel in Kategorien unterteilt (mathematisch, finanziell, statistisch usw.).
Der Aufruf jeder Funktion besteht aus zwei Teilen: dem Namen der Funktion und den Argumenten in Klammern.

Tisch. Integrierte Funktionen in Excel

* Ohne Argumente geschrieben.

Tisch . Arten von Links

Übung.

1. Die Kosten von 1 kWh sind festgelegt. Strom- und Zählerstände für den vergangenen und aktuellen Monat. Es ist notwendig, den Stromverbrauch für den vergangenen Zeitraum und die Kosten des verbrauchten Stroms zu berechnen.

Arbeitstechnik:

1. Text in Zellen ausrichten. Markieren Sie die Zellen A3:E3. Home - Format - Zellenformat - Ausrichtung: horizontal - zentriert, vertikal - zentriert, Anzeige - Zeilenumbruch.

2. Geben Sie in Zelle A4 Folgendes ein: Sq. 1, geben Sie in Zelle A5 ein: Sq. 2. Wählen Sie die Zellen A4:A5 aus und verwenden Sie die Markierung für die automatische Vervollständigung, um die Nummerierung der Wohnungen bis einschließlich 7 einzugeben.

5. Füllen Sie die Zellen B4:C10 gemäß dem Bild aus.

6. Geben Sie in Zelle D4 die Formel zum Ermitteln des Strom- / Energieverbrauchs ein. Und füllen Sie die Zeilen unten mit dem Autocomplete-Token aus.

7. Geben Sie in Zelle E4 die Formel zur Ermittlung der Stromkosten ein=D4*$B$1. Und füllen Sie die Zeilen unten mit dem Autocomplete-Token aus.

Beachten Sie!
Beim automatischen Ausfüllen ändert sich die Adresse der Zelle B1 nicht,
Weil es wird ein absoluter Bezug gesetzt.

8. Geben Sie in Zelle A11 den Text „Statistische Daten“ ein, wählen Sie die Zellen A11:B11 aus und klicken Sie in der Symbolleiste auf die Schaltfläche „Zusammenführen und zentrieren“.

9. Geben Sie in die Zellen A12:A15 den in der Abbildung gezeigten Text ein.

10. Klicken Sie auf Zelle B12 und geben Sie eine mathematische Funktion einSUMME , klicken Sie dazu in die Bearbeitungsleisteper Zeichenfx und wählen Sie eine Funktion aus und bestätigen Sie den Zellbereich.

11. Ebenso werden Funktionen in den Zellen B13:B15 eingestellt.

12. Sie haben die Berechnungen auf Blatt 1 durchgeführt, benennen Sie es in Elektrizität um.

Selbstständige Arbeit

Übung 1:

Berechnen Sie Ihr Alter vom aktuellen Jahr bis 2030 mit dem Autocomplete-Token. Das Geburtsjahr ist eine absolute Referenz. Führen Sie Berechnungen auf Blatt 2 durch. Benennen Sie Blatt 2 in Alter um.

Übung 2: Erstellen Sie eine Tabelle nach dem Beispiel.In Zellenich5: L12 undD13: L14 sollten Formeln sein: AVERAGE, COUNTIF, MAX, MIN. ZellenB3: H12 werden von Ihnen mit Informationen gefüllt.

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 5.docx

Bibliothek
Materialien

Praktische Arbeit 5

Wenn Sie dieses Thema bearbeiten, lernen Sie:

 Technologien zum Erstellen eines Tabellenkalkulationsdokuments;

 Weisen Sie den verwendeten Daten einen Typ zu;

 Erstellen von Formeln und Regeln zum Ändern von Links in ihnen;

 Verwenden Sie die in Excel integrierten statistischen Funktionen für Berechnungen.

Übung 1. Berechnen Sie die Anzahl der gelebten Tage.

Arbeitstechnik:

1. Starten Sie die Excel-Anwendung.

2. Geben Sie in Zelle A1 Ihr Geburtsdatum ein (Tag, Monat, Jahr - 20.12.97). Dateneingabe korrigieren.

3. Sehen Sie sich verschiedene Datumsformate an(Home - Zellenformat - Andere Zahlenformate - Datum) . Datum in Typ umwandelnHH.MM.JJJJ. Beispiel 14.03.2001

4. Betrachten Sie mehrere Arten von Datumsformaten in Zelle A1.

5. Geben Sie das heutige Datum in Zelle A2 ein.

6. Berechnen Sie in Zelle A3 die Anzahl der gelebten Tage mit der Formel. Das Ergebnis kann als Datum dargestellt werden, in diesem Fall sollte es in einen numerischen Typ konvertiert werden.

Aufgabe 2. Das Alter der Schüler. Nach einer vorgegebenen Liste von Studenten und deren Geburtsdatum. Bestimmen Sie, wer früher (später) geboren wurde, bestimmen Sie, wer der Älteste (Jüngste) ist.

Arbeitstechnik:

1. Holen Sie sich die Datei Age. Per LAN: Öffnen Sie den Ordner „Netzwerkumgebung“ -Chef-Allgemeine Dokumente - Klasse 9, finden Sie die Altersakte. Kopieren Sie es auf eine Ihnen bekannte Weise oder laden Sie es von dieser Seite unten in der Anwendung herunter.

2. Berechnen Sie das Alter der Schüler. Um das Alter zu berechnen, müssen Sie die Funktion verwendenHEUTE Highlight heute aktuelles Datum das Geburtsdatum des Schülers wird davon subtrahiert, dann wird aus dem resultierenden Datum unter Verwendung der YEAR-Funktion nur das Jahr aus dem Datum extrahiert. Subtrahieren Sie von der resultierenden Zahl 1900 - Jahrhundert und erhalten Sie das Alter des Schülers. Schreiben Sie in Zelle D3 die Formel=JAHR(HEUTE()-C3)-1900 . Das Ergebnis kann als Datum dargestellt werden, in welchem ​​Fall es umgerechnet werden solltenumerischer Typ.

3. Ermitteln Sie den frühesten Geburtstag. Schreiben Sie in Zelle C22 die Formel=MIN(C3:C21) ;

4. Bestimmen Sie den jüngsten Schüler. Schreiben Sie in Zelle D22 die Formel=MIN(D3:D21) ;

5. Ermitteln Sie den spätesten Geburtstag. Schreiben Sie in Zelle C23 die Formel=MAX(C3:C21) ;

6. Ermitteln Sie den ältesten Schüler. Schreiben Sie in Zelle D23 die Formel=MAX(D3:D21) .

Selbstständige Arbeit:
Eine Aufgabe. Führen Sie die erforderlichen Berechnungen zum Wachstum der Studenten durch verschiedene Einheiten Messungen.

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 6.docx

Bibliothek
Materialien

Praktische Arbeit 6

MS-Excel. Statistische Funktionen“ Teil II.

Aufgabe 3. Verarbeiten Sie die Daten mithilfe einer Tabellenkalkulation mit statistischen Funktionen. Es werden Informationen über die Schüler der Klasse gegeben, einschließlich der Durchschnittsnote für das Quartal, Alter (Geburtsjahr) und Geschlecht. Ermitteln Sie den Notendurchschnitt der Jungen, den Anteil der sehr guten Schülerinnen und Schüler und den Unterschied im Notendurchschnitt der Schülerinnen und Schüler unterschiedlichen Alters.

Lösung:
Füllen Sie die Tabelle mit den Anfangsdaten aus und führen Sie die erforderlichen Berechnungen durch.Achten Sie auf das Format der Werte in den Zellen „GPA“ (numerisch) und „Geburtsdatum“ (Datum)

Die Tabelle verwendet zusätzliche Spalten, die zur Beantwortung der in der Aufgabe gestellten Fragen erforderlich sind -Alter des Schülers und ist der Schülerausgezeichnete Studentin und Mädchen gleichzeitig.
Zur Berechnung des Alters wurde folgende Formel verwendet (am Beispiel der Zelle G4):

=GANZZAHL((HEUTE()-E4)/365,25)

Kommentieren wir es. Das Geburtsdatum des Schülers wird vom heutigen Datum abgezogen. So erhalten wir die Gesamtzahl der Tage, die seit der Geburt des Schülers vergangen sind. Teilen Sie diese Zahl durch 365,25 (die tatsächliche Anzahl der Tage in einem Jahr, 0,25 Tage für ein normales Jahr werden durch ein Schaltjahr ausgeglichen), erhalten wir die Gesamtzahl der Jahre des Studenten; schließlich den ganzen Teil hervorheben, - das Alter des Schülers.

Ob ein Mädchen eine hervorragende Schülerin ist, bestimmt die Formel (am Beispiel von Zelle H4):

=WENN(UND(D4=5,F4="w");1,0)

Beginnen wir mit den grundlegenden Berechnungen.
Zunächst ist es erforderlich, die durchschnittliche Punktzahl der Mädchen zu bestimmen. Laut Definition ist es notwendig, die Gesamtpunktzahl der Mädchen durch ihre Anzahl zu teilen. Dazu können Sie die entsprechenden Funktionen des Tabellenkalkulationsprogramms verwenden.

=SUMMEWENN(F4:F15,"W",D4:D15)/ZÄHLENWENN(F4:F15,"W")

Mit der SUMMEWENN-Funktion können Sie die Werte nur in den Zellen des Bereichs summieren, die das angegebene Kriterium erfüllen (in unserem Fall ist das Kind ein Junge). Die COUNTIF-Funktion zählt die Anzahl der Werte, die die angegebenen Kriterien erfüllen. So bekommen wir, was wir brauchen.
Um den Anteil der exzellenten Schülerinnen an allen Mädchen zu berechnen, werden wir die Anzahl der exzellenten Mädchen der Gesamtzahl der Mädchen zuordnen (hier verwenden wir den Wertesatz aus einer der Hilfsspalten):

=SUMME(H4:H15)/ZÄHLENWENN(F4:F15,"W")

Schließlich bestimmen wir den Unterschied in den durchschnittlichen Punktzahlen von Kindern unterschiedlichen Alters (wir werden die Hilfsspalte in den Berechnungen verwendenDas Alter ):

=ABS(SUMMEWENN(G4:G15,15,D4:D15)/ZÄHLENWENN(G4:G15,15)-
SUMMEWENN(G4:G15,16,D4:D15)/ZÄHLENWENN(G4:G15,16))

Bitte beachten Sie, dass das Datenformat in den Zellen G18:G20 numerisch ist, zwei Dezimalstellen. Somit ist das Problem vollständig gelöst. Die Abbildung zeigt die Ergebnisse der Lösung für einen gegebenen Datensatz.

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 7.docx

Bibliothek
Materialien

Praktische Arbeit 7

"Diagramme mit MS Excel erstellen"

Wenn Sie dieses Thema bearbeiten, lernen Sie:

 Ausführen von Operationen zum Erstellen von Diagrammen basierend auf den in die Tabelle eingegebenen Daten;

 Bearbeiten Sie Diagrammdaten, ihren Typ und ihr Aussehen.

Was ist ein Diagramm. Das Diagramm dient der grafischen Darstellung der Daten. Linien, Balken, Spalten, Sektoren und andere visuelle Elemente werden verwendet, um in Tabellenzellen eingegebene numerische Daten anzuzeigen. Das Aussehen eines Diagramms hängt von seinem Typ ab. Alle Diagramme, mit Ausnahme des Tortendiagramms, haben zwei Achsen: Die horizontale ist die Kategorienachse und die vertikale die Werteachse. Beim Erstellen von 3D-Diagrammen wird eine dritte Achse hinzugefügt – die Reihenachse. Häufig enthält ein Diagramm Elemente wie ein Raster, Titel und eine Legende. Gitterlinien sind Erweiterungen der Unterteilungen auf den Achsen, Titel werden verwendet, um die einzelnen Elemente des Diagramms und die Art der darin dargestellten Daten zu erläutern, die Legende hilft bei der Identifizierung der im Diagramm dargestellten Datenreihen. Es gibt zwei Möglichkeiten, Diagramme hinzuzufügen: Betten Sie sie in das aktuelle Arbeitsblatt ein und fügen Sie ein separates Diagrammblatt hinzu. Falls das Diagramm selbst von Interesse ist, wird es auf einem separaten Blatt platziert. Wenn Sie gleichzeitig das Diagramm und die Daten anzeigen müssen, auf deren Grundlage es erstellt wurde, wird ein eingebettetes Diagramm erstellt.

Das Diagramm wird gespeichert und mit der Arbeitsmappe gedruckt.

Nachdem das Diagramm erstellt wurde, können Änderungen daran vorgenommen werden. Bevor Sie irgendwelche Aktionen mit Diagrammelementen durchführen, wählen Sie sie aus, indem Sie mit der linken Maustaste darauf klicken. Rufen Sie danach das Kontextmenü mit der rechten Maustaste auf oder verwenden Sie die entsprechenden SchaltflächenDiagramm-Symbolleiste .

Eine Aufgabe: Verwenden Sie die Tabellenkalkulation, um die Funktion Y=3,5x–5 zu zeichnen. Wobei X Werte von -6 bis 6 in Schritten von 1 annimmt.

Arbeitstechnik:

1. Starten Sie die Excel-Tabelle.

2. Geben Sie in Zelle A1 "X" ein, in Zelle B1 "Y".

3. Markieren Sie den Zellbereich A1:B1, richten Sie den Text in den Zellen zentriert aus.

4. Geben Sie in Zelle A2 -6 und in Zelle A3 -5 ein. Füllen Sie die Zellen unten mit der Autofill-Markierung bis zu Parameter 6 aus.

5. Geben Sie in Zelle B2 die Formel ein: =3,5*A2–5. Verwenden Sie das Token für die automatische Vervollständigung, um diese Formel bis zum Ende der Datenparameter zu erweitern.

6. Wählen Sie die gesamte Tabelle aus, die Sie erstellt haben, und legen Sie ihre äußeren und inneren Grenzen fest.

7. Wählen Sie den Tabellenkopf aus und füllen Sie den inneren Bereich.

8. Wählen Sie die restlichen Tabellenzellen aus und füllen Sie den inneren Bereich mit einer anderen Farbe.

9. Wählen Sie die gesamte Tabelle aus. Wählen Sie in der Menüleiste Einfügen -Diagramm , Typ: Spot, Ansicht: Spot mit glatten Kurven.

10. Verschieben Sie das Diagramm unter die Tabelle.

Selbstständige Arbeit:

Zeichnen Sie die Funktion y=Sünde(x)/ xauf dem Segment [-10;10] mit einem Schritt von 0,5.

Zeigen Sie den Graphen der Funktion an: a) y=x; b) y=x 3 ; c) y=-x auf dem Segment [-15;15] mit Schritt 1.

Öffnen Sie die Datei "Städte" (gehen Sie zum Netzwerkordner - 9. Klasse-Städte).

Berechnen Sie die Gesprächskosten ohne Rabatt (Spalte D) und die Gesprächskosten mit Rabatt (Spalte F).

Erstellen Sie für eine visuelle Darstellung zwei Tortendiagramme. (1- Diagramm der Kosten eines Gesprächs ohne Rabatt; 2- Diagramm der Kosten eines Gesprächs mit Rabatt).

Ausgewähltes Dokument zum Anzeigen Excel pr.r. 8.docx

Bibliothek
Materialien

Praktische Arbeit 8

GRAFIK UND ZEICHNUNGEN MIT MITTELN ERSTELLEN MS EXCEL

1. Erstellen einer Zeichnung"REGENSCHIRM"

Hier sind die Funktionen, deren Graphen an diesem Bild beteiligt sind:

y1= -1/18x 2 + 12, xО[-12;12]

j2= ​​​​-1/8x 2 +6, xО[-4;4]

j3= -1/8(x+8) 2 + 6, xО[-12; -vier]

j4= -1/8(x-8) 2 + 6, хО

j5= 2(x+3) 2 – 9, хн[-4;0]

j6=1.5(x+3) 2 – 10, xО[-4;0]

- Führen Sie MS EXCEL aus

- In einer ZelleA1 Variablenbezeichnung eingebenX

· - Füllen Sie den Zellbereich A2:A26 mit Zahlen von -12 bis 12 aus.

Für jeden Graphen der Funktion werden wir nacheinander Formeln einführen. Für y1= -1/8x 2 + 12, xО[-12;12], z
j2= ​​​​-1/8x 2 +6, xО[-4;4] usw.

Die Reihenfolge der Aktionen:

Setzen Sie den Cursor auf eine ZelleIN 1 und eintreteny1

Zur ZelleIN 2 geben Sie die Formel ein=(-1/18)*A2^2 +12

Klicken Eintreten auf Tastatur

Der Wert der Funktion wird automatisch berechnet.

Erweitern Sie die Formel auf Zelle A26

Ebenso in einer ZelleC10 (da wir den Wert der Funktion nur auf dem Intervall x von [-4; 4] finden) geben wir die Formel für den Graphen der Funktion einj2= ​​​​-1/8x 2 +6. USW.

Das Ergebnis sollte die folgende ET sein

Nachdem alle Werte der Funktionen berechnet wurden, können SieGraphen bauen dieseFunktionen

Wählen Sie den Zellbereich A1 aus:G26

Wählen Sie in der Symbolleiste ausMenü einfügen → Diagramm

Wählen Sie im Fenster des Diagrammassistenten ausSpot → Gewünschte Ansicht auswählen → klicken OK .

Das Ergebnis sollte das folgende Bild sein:

Aufgabe für Einzelarbeit:

Zeichnen Sie die Graphen der Funktionen in einem Koordinatensystem.x von -9 bis 9 in 1er-Schritten . Holen Sie sich eine Zeichnung.

1. "Punkte"

2. "Katze" Daten filtern (auswählen). in der Tabelle können Sie nur die Zeilen anzeigen, deren Zelleninhalte die angegebene Bedingung oder mehrere Bedingungen erfüllen. Anders als beim Sortieren werden die Daten beim Filtern nicht umsortiert, sondern es werden nur die Datensätze ausgeblendet, die die angegebenen Auswahlkriterien nicht erfüllen.

Die Datenfilterung kann auf zwei Arten erfolgen:Verwenden Sie den Autofilter oder den erweiterten Filter.

Um den Autofilter zu verwenden, benötigen Sie:

Ö setzen Sie den Cursor in die Tabelle;

Ö wähle eine MannschaftDaten - Filter - Autofilter;

Ö Erweitern Sie die Liste der Spalte, nach der die Auswahl getroffen wird.

Ö Wählen Sie einen Wert oder eine Bedingung aus und legen Sie die Auswahlkriterien im Dialogfeld festBenutzerdefinierter Autofilter.

Um alle Zeilen der Quelltabelle wiederherzustellen, wählen Sie die Zeile alle in der Dropdown-Liste des Filters oder wählen Sie den BefehlDaten - Filter - Alle anzeigen.

Um den Filtermodus aufzuheben, platzieren Sie den Cursor innerhalb der Tabelle und wählen Sie den Menübefehl erneutDaten - Filter - Autofilter (deaktivieren).

Mit einem erweiterten Filter können Sie mehrere Auswahlkriterien erstellen und eine komplexere Filterung von Tabellenkalkulationsdaten durchführen, indem Sie eine Reihe von Kriterien zur Auswahl für mehrere Spalten angeben. Das Filtern von Datensätzen mit dem erweiterten Filter erfolgt über den MenübefehlDaten - Filter - Erweiterter Filter.

Übung.

Erstellen Sie eine Tabelle gemäß dem in der Abbildung gezeigten Beispiel. Speichern Sie es als Sort.xls.

Technologie zur Aufgabenausführung:

1. Öffnen Sie das Dokument Sort.xls

2.

3. Führen Sie einen Menübefehl ausDaten - Sortierung.

4. Wählen Sie den ersten Sortierschlüssel „Aufsteigend“ (Alle Abteilungen in der Tabelle werden alphabetisch sortiert).

Denken Sie daran, dass wir jeden Tag eine Liste der im Geschäft verbleibenden Artikel (mit einem Guthaben ungleich Null) ausdrucken müssen, aber dazu müssen wir zuerst eine solche Liste erhalten, d.h. Filtern Sie die Daten.

5. Setzen Sie den Rahmencursor in die Datentabelle.

6. Führen Sie einen Menübefehl ausDaten - Filtern

7. Deaktivieren Sie die Tabellen.

8. Jede Zelle des Tabellenkopfes hat jetzt eine Schaltfläche "Pfeil nach unten", sie wird nicht gedruckt, sodass Sie die Filterkriterien festlegen können. Wir wollen alle Einträge mit einem Rest ungleich Null belassen.

9. Klicken Sie auf die Pfeilschaltfläche, die in der Spalte erscheintRestbetrag . Es öffnet sich eine Liste zur Auswahl. Zeile auswählenBedingung. Bedingung einstellen: > 0. KlickenOK . Die Daten in der Tabelle werden gefiltert.

10. Anstatt von vollständige Liste Waren erhalten wir eine Liste der bisher verkauften Waren.

11. Der Filter kann erweitert werden. Wenn Sie zusätzlich eine Abteilung auswählen, erhalten Sie eine Liste der nicht gelieferten Waren nach Abteilung.

12. Um die Liste aller unverkauften Waren für alle Abteilungen erneut zu sehen, müssen Sie in der Liste „Abteilungen“ das Kriterium „Alle“ auswählen.

13. Um in Ihren Berichten nicht durcheinander zu kommen, fügen Sie ein Datum ein, das sich automatisch entsprechend ändert Systemzeit ComputerFormeln - Funktion einfügen - Datum und Uhrzeit - Heute .

Selbstständige Arbeit

MS-Excel. Statistikfunktionen»

1 Aufgabe (allgemein) (2 Punkte).

Verarbeiten Sie die Daten mithilfe einer Tabellenkalkulation mit statistischen Funktionen.
1. Angaben zu Schülern einer Klasse (10 Personen) inklusive Noten für einen Monat in Mathematik. Zählen Sie die Anzahl der Fünfer, Vierer, Zweier und Dreier, ermitteln Sie die durchschnittliche Punktzahl jedes Schülers und die durchschnittliche Punktzahl der gesamten Gruppe. Erstellen Sie ein Diagramm, das den Prozentsatz der Noten in einer Gruppe darstellt.

2.1 Aufgabe (2 Punkte).

Vier Freunde reisen mit drei Verkehrsmitteln: Zug, Flugzeug und Schiff. Nikolai schwamm 150 km auf einem Dampfer, reiste 140 km mit dem Zug und flog 1.100 km mit dem Flugzeug. Vasily ist 200 km mit einem Dampfer gesegelt, 220 km mit einem Zug gereist und 1.160 km mit einem Flugzeug geflogen. Anatoly flog 1200 km in einem Flugzeug, reiste 110 km in einem Zug und segelte 125 km in einem Dampfschiff. Maria ist 130 km mit dem Zug gereist, 1500 km mit dem Flugzeug geflogen und 160 km mit dem Schiff gesegelt.
Erstellen Sie eine Tabelle basierend auf den obigen Daten.

Fügen Sie der Tabelle eine Spalte hinzu, die die Gesamtzahl der von jedem der Jungs zurückgelegten Kilometer anzeigt.

Berechnen Sie die Gesamtzahl der Kilometer, die die Jungs mit dem Zug gefahren sind, mit dem Flugzeug geflogen sind und mit dem Dampfschiff gesegelt sind (jedes Transportmittel separat).

Berechne die Gesamtkilometer aller Freunde.

Ermitteln Sie für alle Verkehrsmittel die maximale und minimale Kilometerzahl der Freunde.

Bestimmen Sie die durchschnittliche Kilometerzahl für alle Verkehrsträger.

2.2 Aufgabe (2 Punkte).

Erstellen Sie eine Tabelle „Seen Europas“ mit den folgenden Daten für Fläche (km²) und größte Tiefe (m): Ladoga 17.700 und 225; Onega 9510 und 110; Kaspisches Meer 371.000 und 995; Venern 5550 und 100; Tschudskoje mit Pskow 3560 und 14; Balaton 591 und 11; Genf 581 und 310; Vättern 1900 und 119; Konstanz 538 und 252; Mälaren 1140 und 64. Bestimme den flächenmäßig größten und kleinsten See, den tiefsten und flachsten See.

2.3 Aufgabe (2 Punkte).

Erstellen Sie eine Tabelle „Flüsse Europas“ mit den folgenden Längen- (km) und Einzugsgebietsdaten (tausend km²): Wolga 3688 und 1350; Donau 2850 und 817; Rhein 1330 und 224; Elba 1150 und 148; Weichsel 1090 und 198; Loire 1020 und 120; Ural 2530 und 220; Don 1870 und 422; Seine 780 und 79; Themse 340 und 15. Bestimmen Sie die längsten und kürzesten Flüsse, berechnen Sie die Gesamtfläche der Flusseinzugsgebiete, die durchschnittliche Länge der Flüsse im europäischen Teil Russlands.

3 Aufgabe (2 Punkte).

Die Bank erfasst die Pünktlichkeit der Zahlungen von Darlehen, die an mehrere Organisationen vergeben wurden. Der Darlehensbetrag und der von der Organisation bereits gezahlte Betrag sind bekannt. Für Schuldner werden Strafen festgelegt: Wenn das Unternehmen den Kredit zu mehr als 70 Prozent zurückgezahlt hat, beträgt die Strafe 10 Prozent der Schulden, ansonsten 15 Prozent. Berechnen Sie das Bußgeld für jede Organisation, das durchschnittliche Bußgeld, den Gesamtgeldbetrag, den die Bank zusätzlich erhalten wird. Bestimmen Sie die durchschnittliche Geldbuße von Haushaltsorganisationen.

Finden Sie Material für jede Unterrichtsstunde,

Analytical Analytical Processing (OLAP) ist eine Technologie, die verwendet wird, um große Geschäftsdatenbanken zu organisieren und Business Intelligence zu unterstützen. OLAP-Datenbanken sind in einen oder mehrere Cubes unterteilt, und jeder Cube wird vom Cube-Administrator so organisiert, dass er der Art und Weise entspricht, wie Daten abgerufen und analysiert werden, um das Erstellen und Verwenden der benötigten PivotTable- und PivotChart-Berichte zu vereinfachen.

In diesem Artikel

Was ist Business Intelligence?

Ein Business-Analyst möchte sich oft ein umfassenderes Bild des Unternehmens machen, breitere Trends basierend auf aggregierten Daten anzeigen und Trends in eine beliebige Anzahl von Variablen aufschlüsseln. Business Intelligence ist der Prozess des Extrahierens von Daten aus einer OLAP-Datenbank und des Analysierens dieser Daten, um Erkenntnisse zu gewinnen, die verwendet werden können, um fundierte Geschäftsentscheidungen zu treffen und Maßnahmen zu ergreifen. Mit OLAP und Business Intelligence können Sie beispielsweise die folgenden Fragen zu Geschäftsdaten beantworten.

Wie verhält sich der Gesamtumsatz aller Produkte im Jahr 2007 im Vergleich zum Umsatz seit 2006?

Wie verhält sich dies im Vergleich zu Datum und Uhrzeit des Leistungszeitraums in den letzten fünf Jahren?

Wie viel Geld haben Kunden letztes Jahr für 35 ausgegeben und wie hat sich dieses Verhalten im Laufe der Zeit verändert?

Wie viele Produkte wurden diesen Monat in zwei bestimmten Ländern/Regionen verkauft im Vergleich zum gleichen Monat im letzten Jahr?

Für jede Kundenaltersgruppe Wie sieht die Aufschlüsselung der Rentabilität (sowohl Margenprozentsatz als auch Gesamtsumme) nach Produktkategorie aus?

Suchen Sie nach Top- und Bottom-Sellern, Distributoren, Lieferanten, Kunden, Partnern und Kunden.

Was ist Online Analytical Processing (OLAP)?

OLAP-Datenbanken (Online Analytical Processing) vereinfachen Business-Intelligence-Abfragen. OLAP ist eine Datenbanktechnologie, die für Abfragen und Berichte optimiert ist, nicht für die Transaktionsverarbeitung. Die Datenquelle für OLAP sind OLTP-Datenbanken (Online Transaction Processing), die normalerweise in Data Warehouses gespeichert sind. Aus diesen historischen Daten werden OLAP-Daten extrahiert und zu Strukturen kombiniert, die komplexe Analysen ermöglichen. OLAP-Daten werden auch hierarchisch organisiert und in Cubes statt in Tabellen gespeichert. Es ist eine ausgeklügelte Technologie, die mehrdimensionale Strukturen verwendet, um sie bereitzustellen Schneller Zugang zu den Daten zur Analyse. In dieser Organisation können Sie für einen PivotTable-Bericht oder einen PivotChart-Bericht auf einfache Weise zusammenfassende Daten auf hoher Ebene anzeigen, z. B. Verkaufssummen für ein ganzes Land oder eine Region, und auch Details zu Websites mit besonders starken oder schwachen Verkäufen anzeigen.

OLAP-Datenbanken sollen das Laden von Daten beschleunigen. Da OLAP-Server, nicht Microsoft Office-Excel, aggregierte Werte berechnet, müssen beim Erstellen oder Bearbeiten eines Berichts weniger Daten an Excel gesendet werden. Dieser Ansatz ermöglicht es Ihnen, mit einer größeren Menge an Quelldaten zu arbeiten, als wenn die Daten in einer herkömmlichen Datenbank organisiert wären, in der Excel alle einzelnen Datensätze abruft und aggregierte Werte berechnet.

OLAP-Datenbanken enthalten zwei Haupttypen von Daten: Kennzahlen, bei denen es sich um numerische Daten, Mengen und Durchschnittswerte handelt, die zum Treffen fundierter Geschäftsentscheidungen verwendet werden, und Dimensionen, bei denen es sich um Kategorien handelt, die zum Organisieren dieser Kennzahlen verwendet werden. OLAP-Datenbanken helfen Ihnen, Ihre Daten mit vielen Detailebenen zu organisieren, indem Sie dieselben Kategorien verwenden, die Sie aus der Datenanalyse kennen.

In den folgenden Abschnitten wird jede Komponente unten im Detail beschrieben.

Kubisch Eine Datenstruktur, die Kennzahlen über Ebenen und Hierarchien hinweg jeder Dimension kombiniert, die Sie analysieren möchten. Cubes kombinieren mehrere Dimensionen wie Zeit, Geographie und Produktlinien mit zusammenfassenden Daten wie Verkäufen und Inventar. Würfel sind keine "Würfel" im streng mathematischen Sinne, da sie nicht unbedingt die gleichen Seiten haben. Sie stellen jedoch eine treffende Metapher für ein komplexes Konzept dar.

Messungen Ein Satz von Werten in einem Cube, die auf einer Spalte in der Faktentabelle des Cubes basieren und normalerweise ein numerischer Wert sind. Kennzahlen sind zentrale Werte in einem Cube, die vorverarbeitet, verarbeitet und analysiert werden. Die häufigsten Beispiele sind Umsatz, Einnahmen, Einnahmen und Ausgaben.

Mitglied Ein Element in einer Hierarchie, das ein oder mehrere Vorkommen von Daten darstellt. Ein Element kann entweder eindeutig oder nicht eindeutig sein. Beispielsweise stellen 2007 und 2008 eindeutige Elemente auf der Jahresebene der Zeitdimension dar, während Januar nicht eindeutige Elemente auf der Monatsebene darstellt, da es mehr als einen Januar in der Zeitdimension gibt, weil er Daten für mehr als ein Jahr enthält.

Berechnetes Mitglied Ein Element einer Dimension, dessen Wert zur Laufzeit mithilfe eines Ausdrucks ausgewertet wird. Die Werte berechneter Stäbe können aus den Werten anderer Stäbe abgeleitet werden. Beispielsweise kann der berechnete Artikel "Gewinn" bestimmt werden, indem der Wert des Artikels sowie die Kosten vom Wert des Artikels, dem Umsatz, abgezogen werden.

Messung Ein Satz aus einer oder mehreren geordneten Hierarchien auf Cube-Ebene, die der Benutzer versteht und als Grundlage für die Datenanalyse verwendet. Eine geografische Dimension kann beispielsweise die Ebenen Land/Region, Bundesland/Region und Stadt umfassen. Darüber hinaus kann eine Zeitdimension eine Hierarchie mit Jahres-, Quartals-, Monats- und Tagesebenen enthalten. In einem PivotTable- oder PivotChart-Bericht wird jede Hierarchie zu einer Gruppe von Feldern, die erweitert und reduziert werden können, um niedrigere oder höhere Werte anzuzeigen hohe Levels.

Hierarchie Eine logische Baumstruktur, die die Elemente einer Dimension so anordnet, dass jedes Element ein übergeordnetes Element und null oder mehr untergeordnete Elemente hat. Ein untergeordnetes Element ist ein Mitglied einer früheren Gruppe in der Hierarchie, die in direktem Zusammenhang mit dem aktuellen Mitglied steht. Beispielsweise ist in einer Zeithierarchie, die Quartals-, Monats- und Tagesebenen enthält, Januar ein untergeordnetes Element von Qtr1. Das übergeordnete Element ist ein Mitglied von more niedriges Niveau in der Hierarchie, die direkt mit dem aktuellen Mitglied verknüpft ist. Der übergeordnete Wert ist normalerweise eine Konsolidierung der Werte aller untergeordneten Elemente. Beispielsweise ist Qtr1 in einer Zeithierarchie mit Quartals-, Monats- und Tagesebenen das übergeordnete Element von Januar.

Eben In einer Hierarchie können Daten in niedrigere und höhere Detailebenen organisiert werden, z. B. Jahre, Quartale, Monate und Tagesebenen in einer Zeithierarchie.

OLAP-Funktionen in Excel

Abrufen von OLAP-Daten Sie können eine Verbindung zu OLAP-Datenquellen genauso herstellen wie zu anderen externen Datenquellen. Sie können mit Datenbanken arbeiten, die mit Microsoft SQL Server OLAP Services Version 7.0, Microsoft SQL Server Analysis Services Version 2000 und Microsoft SQL Server Analysis Services Version 2005, Microsoft OLAP Server Products, erstellt wurden. Excel kann auch mit OLAP-Produkten von Drittanbietern arbeiten, die mit OLE-DB für OLAP kompatibel sind.

OLAP-Daten können nur als PivotTable- oder PivotChart-Bericht oder in einer aus einem PivotTable-Bericht konvertierten Arbeitsblattfunktion angezeigt werden, nicht als externer Datenbereich. Sie können OLAP-PivotTable- und PivotChart-Berichte in Berichtsvorlagen speichern und ODC-Dateien (Office Data Connection) erstellen, um eine Verbindung mit OLAP-Datenbanken für OLAP-Abfragen herzustellen. Wenn Sie eine ODC-Datei in Excel öffnen, wird ein leerer PivotTable-Bericht angezeigt und kann platziert werden.

Erstellen Sie Cube-Dateien für die Offline-Nutzung Sie können eine eigenständige Cube-Datei (.cub) mit einer Teilmenge von Daten aus einer OLAP-Server-Datenbank erstellen. Offline-Cube-Dateien werden verwendet, um mit OLAP-Daten zu arbeiten, wenn Sie nicht mit einem Netzwerk verbunden sind. Mit einem Cube können Sie mit mehr Daten in einem PivotTable- oder PivotChart-Bericht arbeiten als sonst, und die Daten schneller abrufen. Sie können Cube-Dateien nur erstellen, wenn Sie einen OLAP-Anbieter wie Microsoft SQL Analysis Services Version 2005 verwenden, der diese Funktion unterstützt.

Serveraktionen Eine Serveraktion ist eine optionale Funktion, die ein OLAP-Cube-Administrator auf einem Server definieren kann, der ein Cube-Element oder eine Kennzahl als Parameter in einer Abfrage verwendet, um Informationen in einem Cube abzurufen oder eine andere Anwendung wie einen Browser zu starten. Excel unterstützt URLs, Berichte, Rowsets, Drilldown und Drilldown detaillierter Serveraktionen, unterstützt jedoch keine nativen nativen Operatoren und Datensätze.

KPI Ein KPI ist eine spezielle, auf dem Server definierte berechnete Kennzahl, mit der Sie „Key Performance Indicators“ verfolgen können, einschließlich des Status (der aktuelle Wert entspricht einer bestimmten Zahl). und Trend (Werte über die Zeit). Wenn sie angezeigt werden, kann der Server die entsprechenden Symbole senden, ähnlich wie das neue Excel-Symbol, um sich über oder unter den Statusebenen auszurichten (z. B. für ein Stopp-Symbol) sowie den Wert nach oben oder unten zu scrollen (z beispielsweise ein Richtungspfeilsymbol).

Formatierung auf dem Server Cube-Administratoren können Kennzahlen und berechnete Elemente mithilfe von Farbformatierungs-, Schriftartformatierungs- und bedingten Formatierungsregeln erstellen, die als Unternehmensstandard-Geschäftsregel zugewiesen werden können. Beispielsweise könnte ein serverseitiges Einkommensformat ein Zahlenwährungsformat sein, die Zellenfarbe ist grün, wenn der Wert größer oder gleich 30.000 ist, und rot, wenn der Wert kleiner als 30.000 ist, und der Schriftstil ist fett, wenn die Wert ist kleiner als 30.000, und wenn der Wert positiv ist - normal. größer oder gleich 30.000 Weitere Informationen finden Sie unter .

Sprache der Office-Benutzeroberfläche Ein Cube-Administrator kann Übersetzungen für Daten und Fehler auf dem Server für Benutzer definieren, die PivotTable-Informationen in einer anderen Sprache anzeigen müssen. Diese Funktion ist als Dateiverbindungseigenschaft definiert, und das Gebietsschema und das Land des Computers des Benutzers müssen mit der Sprache der Benutzeroberfläche übereinstimmen.

Erforderliche Softwarekomponenten für den Zugriff auf OLAP-Datenquellen

OLAP-Anbieter Um OLAP-Datenquellen für Excel einzurichten, benötigen Sie einen der folgenden OLAP-Provider.

Microsoft OLAP-Anbieter Excel enthält einen Datenquellentreiber und Clientsoftware für den Zugriff auf Datenbanken, die mit Microsoft SQL Server Olap Version 7.0, Microsoft SQL Server Olap Version 2000 (8.0) und Microsoft SQL Server Analysis Services Version 2005 (9.0) erstellt wurden.

OLAP-Drittanbieter Für andere OLAP-Produkte müssen zusätzliche Treiber und Client-Software installiert werden. Um die OLAP-Datenbearbeitungsfunktionen von Excel verwenden zu können, muss ein Drittanbieterprodukt dem OLE-DB für OLAP-Standard entsprechen und mit Microsoft Office kompatibel sein. Informationen zum Installieren und Verwenden eines Drittanbieter-OLAP-Anbieters finden Sie unter Systemadministrator oder ein Anbieter von OLAP-Produkten.

Serverdatenbanken und Cube-Dateien Die Excel OLAP-Clientsoftware unterstützt Verbindungen zu zwei Arten von OLAP-Datenbanken. Wenn die Datenbank auf dem OLAP-Server im Netzwerk verfügbar ist, können Sie Quelldaten direkt von dort abrufen. Wenn Sie über eine eigenständige Cube-Datei verfügen, die OLAP-Daten oder eine Cube-Definitionsdatei enthält, können Sie eine Verbindung zu dieser Datei herstellen und daraus Quelldaten abrufen.

Datenquellen Eine Datenquelle bietet Zugriff auf alle Daten in einer OLAP-Datenbank oder einer Offline-Cube-Datei. Nachdem Sie eine OLAP-Datenquelle erstellt haben, können Sie Berichte darauf aufbauen und OLAP-Daten als PivotTable- oder PivotChart-Bericht oder als aus einem PivotTable-Bericht konvertierte Blattfunktion an Excel zurückgeben.

Microsoft-Abfrage Mit Query können Sie Daten aus einer externen Datenbank wie Microsoft SQL oder Microsoft Access. Sie müssen keine Abfrage verwenden, um Daten aus einer OLAP-PivotTable abzurufen, die einer Cube-Datei zugeordnet ist. Zusätzliche Information .

Unterschiede in OLAP-Funktionen und Nicht-OLAP-Quelldaten

Wenn Sie mit PivotTable-Berichten und PivotCharts aus OLAP-Quelldaten und anderen Arten von Quelldaten arbeiten, werden Sie einige Funktionsunterschiede feststellen.

Datenextraktion Der OLAP-Server gibt immer dann neue Daten an Excel zurück, wenn sich das Berichtslayout ändert. Bei anderen Arten von externen Datenquellen fragen Sie alle Quelldaten auf einmal ab, oder Sie können Parameter für die Abfrage nur dann festlegen, wenn verschiedene Berichtsfilterfeldelemente angezeigt werden. Darüber hinaus haben Sie mehrere weitere Optionen zum Aktualisieren des Berichts.

In Berichten, die auf OLAP-Quelldaten basieren, sind Berichtsfilterfeldoptionen nicht verfügbar, Hintergrundabfragen sind nicht verfügbar und die Speicheroptimierungsoption ist nicht verfügbar.

Notiz: Die Speicheroptimierungsoption ist auch nicht für OLEDB-Datenquellen und PivotTable-Berichte verfügbar, die auf einem Bereich von Zellen basieren.

Feldtypen OLAP-Quelldaten. Dimensionsfelder können nur als Zeilen (Zeilen), Spalten (Kategorie) oder Seitenfelder verwendet werden. Kennzahlfelder können nur als Wertfelder verwendet werden. Für andere Arten von Quelldaten können alle Felder in jedem Teil des Berichts verwendet werden.

Zugriff auf detaillierte Daten Für OLAP-Quelldaten definiert der Server verfügbare Ebenen Drilldown und berechnet Zusammenfassungswerte, sodass die Detaildatensätze, aus denen die Zusammenfassungswerte bestehen, möglicherweise nicht verfügbar sind. Der Server kann jedoch Eigenschaftsfelder bereitstellen, die Sie anzeigen können. Andere Quelldatentypen haben keine Eigenschaftsfelder, aber Sie können grundlegende Informationen für Datenfeld- und Elementwerte sowie Elemente ohne Daten anzeigen.

OLAP-Berichtsfilterfelder haben möglicherweise keine alle Elemente und Befehle Berichtsfilterseiten anzeigen Nicht verfügbar.

Anfängliche Sortierreihenfolge Bei OLAP-Quelldaten werden die Elemente zunächst in der Reihenfolge angezeigt, in der sie vom OLAP-Server zurückgegeben werden. Sie können die Elemente sortieren oder manuell neu anordnen. Bei anderen Arten von Quelldaten werden die Elemente des neuen Berichts zunächst nach Elementnamen in aufsteigender Reihenfolge sortiert.

Nimi OLAP-Server liefern Zusammenfassungswerte direkt an den Bericht, daher ist es nicht möglich, die Zusammenfassungsfunktionen für Wertfelder zu ändern. Bei anderen Quelldatentypen können Sie die Aggregatfunktion für das Wertfeld ändern und mehrere Zusammenfassungsfunktionen für dasselbe Wertfeld verwenden. Sie können keine berechneten Felder und berechneten Elemente in Berichten mit OLAP-Quelldaten erstellen.

Zwischensummen In Berichten mit OLAP-Quelldaten können Sie die Zusammenfassungsfunktion für Zwischensummen nicht ändern. Bei anderen Arten von Quelldaten können Sie die Zusammenfassungsfunktionen für Zwischensummen ändern und Zwischensummen für alle Zeilen- und Spaltenfelder ein- oder ausblenden.

Bei OLAP-Quelldaten können Sie bei der Berechnung von Zwischensummen und Gesamtsummen ausgeblendete Elemente ein- oder ausschließen. Bei anderen Quelldatentypen können Sie ausgeblendete Berichtsfilterfeldelemente in Zwischensummen einbeziehen, aber ausgeblendete Elemente in anderen Feldern werden standardmäßig ausgeschlossen.