Suchvorgänge, Diagramme, Statistiken und Pivot-Tabellen
Nachdem wir grundlegende Funktionen, Zellbezüge sowie Datums- und Zeitfunktionen durchgesehen haben, befassen wir uns nun mit einigen der erweiterten Funktionen von Microsoft Excel. Wir präsentieren Methoden zur Lösung klassischer Probleme in den Bereichen Finanzen, Verkaufsberichte, Versandkosten und Statistiken.
SCHULNAVIGATION- Warum benötigen Sie Formeln und Funktionen??
- Formel definieren und erstellen
- Relative und absolute Zellreferenz und Formatierung
- Nützliche Funktionen, die Sie kennenlernen sollten
- Suchvorgänge, Diagramme, Statistiken und Pivot-Tabellen
Diese Funktionen sind für Unternehmen, Studenten und diejenigen, die einfach mehr erfahren möchten, wichtig.
VLOOKUP und HLOOKUP
Hier ein Beispiel zur Veranschaulichung der Funktionen Vertikal Lookup (VLOOKUP) und Horizontal Lookup (HLOOKUP). Diese Funktionen werden verwendet, um eine Zahl oder einen anderen Wert in etwas Verständliches zu übersetzen. Beispielsweise können Sie VLOOKUP verwenden, um eine Teilenummer zu erhalten und die Artikelbeschreibung zurückzugeben.
Um dies zu untersuchen, gehen wir zurück zu unserer „Decision Maker“ -Tabelle in Teil 4, in der Jane versucht zu entscheiden, was sie in der Schule tragen soll. Sie interessiert sich nicht mehr für das, was sie trägt, da sie einen neuen Freund gelandet hat, und trägt nun zufällige Outfits und Schuhe.
In Jane's Kalkulationstabelle listet sie Outfits in vertikalen Spalten und Schuhen auf, horizontale Spalten.
Sie öffnet die Kalkulationstabelle und die Funktion RANDBETWEEN (1,3) generiert eine Zahl zwischen oder gleich eins und drei, die den drei Arten von Outfits entspricht, die sie tragen kann.
Sie verwendet die Funktion RANDBETWEEN (1,5), um zwischen fünf Arten von Schuhen auszuwählen.
Da Jane keine Nummer tragen kann, muss diese in einen Namen umgewandelt werden. Daher verwenden wir Suchfunktionen.
Wir verwenden die VLOOKUP-Funktion, um die Outfit-Nummer in den Outfit-Namen zu übersetzen. HLOOKUP übersetzt von der Schuhnummer zu den verschiedenen Schuharten in der Reihe.
Die Tabelle funktioniert für Outfits folgendermaßen:
Excel wählt eine Zufallszahl von eins bis drei, da sie drei Ausstattungsoptionen hat.
Als nächstes übersetzt die Formel die Zahl in Text mit Hilfe von = VLOOKUP (B11, A2: B4,2), wobei der Wert von B11 mit einer Zufallszahl im Bereich A2: B4 gesucht wird. Es gibt dann das Ergebnis (C11) aus den in der zweiten Spalte aufgelisteten Daten.
Wir verwenden die gleiche Technik zum Auswählen von Schuhen, mit der Ausnahme, dass wir VOOKUP anstelle von HLOOKUP verwenden.
Beispiel: Grundlegende Statistiken
Fast jeder kennt eine Formel aus der Statistik - Durchschnitt -, aber es gibt eine andere Statistik, die für das Geschäft wichtig ist: Standardabweichung.
Viele Menschen, die zum College gegangen sind, haben sich beispielsweise wegen ihrer SAT-Punktzahl gequält. Sie möchten vielleicht wissen, wie sie im Vergleich zu anderen Schülern stehen. Die Universitäten wollen das auch wissen, weil viele Universitäten, vor allem angesehene, Studenten mit niedrigen SAT-Werten ablehnen.
Wie würden wir oder eine Universität SAT-Werte messen und interpretieren? Nachfolgend finden Sie die SAT-Ergebnisse für fünf Schüler zwischen 1.870 und 2.230.
Die wichtigen Zahlen, die man verstehen muss, sind:
Durchschnittlich - Durchschnitt wird auch als "Mittelwert" bezeichnet.
Standardabweichung (STD oder σ) - Diese Zahl zeigt, wie weit ein Satz von Zahlen verteilt ist. Wenn die Standardabweichung groß ist, sind die Zahlen weit auseinander und wenn sie Null ist, sind alle Zahlen gleich. Man könnte sagen, dass die Standardabweichung die durchschnittliche Differenz zwischen dem Durchschnittswert und dem beobachteten Wert ist, d. H. 1.998 und jeder SAT-Wert. Bitte beachten Sie, dass es üblich ist, die Standardabweichung mit dem griechischen Symbol Sigma „σ“ abzukürzen.
Prozentsatz Rang - Wenn ein Student eine hohe Punktzahl erhält, kann er damit prahlen, dass er sich im oberen 99-Perzentil oder so ähnlich befindet. "Percentile-Rang" bedeutet, dass der Prozentsatz der Ergebnisse unter einem bestimmten Wert liegt.
Standardabweichung und Wahrscheinlichkeit sind eng miteinander verbunden. Sie können sagen, dass für jede Standardabweichung die Wahrscheinlichkeit oder Wahrscheinlichkeit, dass diese Anzahl innerhalb dieser Anzahl von Standardabweichungen liegt, gilt:
| STD | Prozentsatz der Ergebnisse | Bereich der SAT-Ergebnisse |
| 1 | 68% | 1,854-2,142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99,73% | 1,567-2,429 |
| 4 | 99,994% | 1,424-2,572 |
Wie Sie sehen, ist die Wahrscheinlichkeit, dass ein SAT-Score außerhalb von 3 STDs liegt, praktisch Null, da 99,73 Prozent der Scores innerhalb von 3 STDs liegen.
Lassen Sie uns nun die Tabelle erneut betrachten und erklären, wie sie funktioniert.
Nun erklären wir die Formeln:
= DURCHSCHNITT (B2: B6)
Der Durchschnitt aller Bewertungen im Bereich B2: B6. Insbesondere die Summe aller Ergebnisse geteilt durch die Anzahl der Personen, die den Test absolviert haben.
= STDEV.P (B2: B6)
Die Standardabweichung über den Bereich B2: B6. Das ".P" bedeutet, dass STDEV.P für alle Bewertungen verwendet wird, d. H. Für die gesamte Population und nicht nur für eine Teilmenge.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Dies berechnet den kumulativen Prozentsatz über den Bereich B2: B6 basierend auf der SAT-Bewertung, in diesem Fall B2. Zum Beispiel liegen 83 Prozent der Ergebnisse unter der von Walker.
Ergebnisse grafisch darstellen
Durch das Einfügen der Ergebnisse in ein Diagramm wird das Verständnis der Ergebnisse vereinfacht. Außerdem können Sie sie in einer Präsentation anzeigen, um Ihre Aussage klarer darzustellen.
Die Schüler befinden sich auf der horizontalen Achse und ihre SAT-Werte werden als blaues Balkendiagramm auf einer Skala (vertikale Achse) von 1.600 bis 2.300 angezeigt.
Die Perzentilrangfolge ist die rechte vertikale Achse von 0 bis 90 Prozent und wird durch die graue Linie dargestellt.
So erstellen Sie ein Diagramm
Das Erstellen eines Diagramms ist ein Thema für sich, wir werden jedoch kurz erklären, wie das obige Diagramm erstellt wurde.
Wählen Sie zunächst den Zellbereich aus, der im Diagramm enthalten sein soll. In diesem Fall A2 bis C6, weil wir sowohl die Zahlen als auch die Namen der Schüler haben wollen.
Wählen Sie im Menü "Einfügen" die Option "Diagramme" -> "Empfohlene Diagramme":
Der Computer empfiehlt ein Diagramm "Clustered-Column, Secondary Axis". Der Teil „Sekundärachse“ bedeutet, dass er zwei vertikale Achsen zeichnet. In diesem Fall wollen wir dieses Diagramm. Wir müssen nichts anderes tun.
Sie können das Diagramm verschieben und die Größe ändern, bis Sie die gewünschte Größe und Position haben. Wenn Sie zufrieden sind, können Sie das Diagramm in der Tabelle speichern.
Wenn Sie mit der rechten Maustaste auf das Diagramm klicken und "Daten auswählen" auswählen, werden die Daten angezeigt, die für den Bereich ausgewählt sind.
Mit der Funktion "Recommended Charts" ("Empfohlene Diagramme") können Sie sich normalerweise nicht mit komplizierten Details auseinandersetzen, wie zum Beispiel zu bestimmen, welche Daten eingeschlossen werden sollen, wie Beschriftungen zugewiesen werden sollen und wie die linke und die rechte vertikale Achse zugewiesen werden.
Klicken Sie im Dialogfeld "Datenquelle auswählen" unter "Legendeneinträge (Serien)" auf "Punktzahl", klicken Sie auf "Bearbeiten" und ändern Sie die Angabe in "Punktzahl".
Ändern Sie dann die Serie 2 ("Perzentil") in "Perzentil".
Kehren Sie zu Ihrem Chart zurück und klicken Sie auf "Chart-Titel" und ändern Sie ihn in "SAT Scores". Jetzt haben wir eine vollständige Chart. Es hat zwei horizontale Achsen: eine für den SAT-Score (blau) und eine für den kumulativen Prozentsatz (orange)..
Beispiel: Das Transportproblem
Das Transportproblem ist ein klassisches Beispiel für eine Art von Mathematik, die als "lineare Programmierung" bezeichnet wird. Auf diese Weise können Sie einen Wert unter bestimmten Bedingungen maximieren oder minimieren. Es hat viele Anwendungen für eine Vielzahl von Geschäftsproblemen. Daher ist es hilfreich zu erfahren, wie es funktioniert.
Bevor wir mit diesem Beispiel beginnen, müssen wir den "Excel Solver" aktivieren.
Aktivieren Sie das Solver-Add-In
Wählen Sie "Datei" -> "Optionen" -> "Add-Ins". Klicken Sie unten in den Add-Ins-Optionen auf die Schaltfläche "Los" neben "Verwalten: Excel-Add-Ins".
Aktivieren Sie im daraufhin angezeigten Menü das Kontrollkästchen "Solver-Add-In" und klicken Sie auf "OK".
Beispiel: Berechnen Sie die niedrigsten iPad-Versandkosten
Angenommen, wir liefern iPads aus und versuchen, unsere Vertriebszentren zu möglichst niedrigen Transportkosten zu füllen. Wir haben eine Vereinbarung mit einer Spedition und Fluggesellschaft getroffen, um iPads von Shanghai, Peking und Hongkong zu den unten aufgeführten Distributionszentren zu versenden.
Der Preis für jedes iPad ist die Entfernung von der Fabrik zum Verteilzentrum zur Anlage geteilt durch 20.000 Kilometer. Zum Beispiel sind es 8.024 km von Shanghai nach Melbourne, das sind 8.024 / 20.000 oder $ 0,40 pro iPad.
Die Frage ist, wie wir all diese iPads von diesen drei Werken zu den niedrigsten möglichen Kosten an diese vier Ziele versenden?
Wie Sie sich vorstellen können, könnte dies ohne Formel und Werkzeug sehr schwierig sein. In diesem Fall müssen wir insgesamt 462.000 (F12) iPads versenden. Die Anlagen haben eine begrenzte Kapazität von 500.250 (G12).
Damit Sie sehen können, wie es funktioniert, haben wir in Zelle B10 1 eingegeben, was bedeutet, dass wir 1 iPad von Shanghai nach Melbourne versenden möchten. Da die Transportkosten auf dieser Route 0,40 $ pro iPad betragen, betragen die Gesamtkosten (B17) 0,40 $.
Die Anzahl wurde mit der Funktion = SUMPRODUCT (Kosten, Versand) berechnet. "Kosten" sind die Bereiche B3: E5.
Und "ausgeliefert" ist die Reihe B9: E11:
SUMPRODUCT multipliziert „Kosten“ mal den Bereich „versendet“ (B14). Dies wird als "Matrixmultiplikation" bezeichnet.
Damit SUMPRODUCT einwandfrei funktioniert, müssen die beiden Matrizen - Kosten und Versand - die gleiche Größe haben. Sie können diese Einschränkung umgehen, indem Sie zusätzliche Kosten erstellen und Spalten und Zeilen mit dem Wert Null versenden, sodass die Arrays dieselbe Größe haben und die Gesamtkosten nicht beeinflusst werden.
Verwenden des Lösers
Wenn wir nur die „Kosten“ der Matrizen multiplizieren müssten, wäre das nicht zu kompliziert, aber wir müssen uns auch mit Einschränkungen beschäftigen.
Wir müssen versenden, was jedes Distributionszentrum benötigt. Wir geben diese Konstante wie folgt in den Löser ein: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Dies bedeutet die Summe der Transporte, d. H. Die Summen in den Zellen $ B $ 12: $ E $ 12 müssen größer oder gleich dem sein, was jedes Verteilzentrum benötigt ($ B $ 13: $ E $ 13)..
Wir können nicht mehr versenden, als wir produzieren. Wir schreiben diese Einschränkungen wie folgt: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Gehen Sie nun zum Menü "Daten" und drücken Sie die Taste "Solver". Wenn die Schaltfläche "Solver" nicht vorhanden ist, müssen Sie das Solver-Add-In aktivieren.
Geben Sie die beiden zuvor beschriebenen Bedingungen ein und wählen Sie den Bereich "Sendungen" aus. Hierbei handelt es sich um den Zahlenbereich, den Excel berechnen soll. Wählen Sie auch den Standardalgorithmus "Simplex LP" aus und geben Sie an, dass wir die Zelle B15 ("Gesamtversandkosten") "minimieren" möchten, wobei dort "Zielsetzung festlegen" angezeigt wird.
Klicken Sie auf "Lösen" und Excel speichert die Ergebnisse in der Kalkulationstabelle, was wir wollen. Sie können dies auch speichern, um mit anderen Szenarien herumzuspielen.
Wenn der Computer angibt, dass er keine Lösung finden kann, haben Sie etwas getan, das nicht logisch ist. Möglicherweise haben Sie mehr iPads angefordert, als die Anlagen produzieren können.
Hier sagt Excel, es habe eine Lösung gefunden. Klicken Sie auf "OK", um die Lösung beizubehalten und zur Tabelle zurückzukehren.
Beispiel: Barwert
Wie entscheidet ein Unternehmen, in ein neues Projekt zu investieren? Ist der „Net Present Value“ (NPV) positiv, werden sie in ihn investieren. Dies ist ein Standardansatz der meisten Finanzanalysten.
Angenommen, das Bergbauunternehmen Codelco möchte die Andinas-Kupfermine erweitern. Der Standardansatz, um zu bestimmen, ob mit einem Projekt fortgefahren werden soll, ist die Berechnung des Kapitalwerts. Ist der Kapitalwert größer als Null, ist das Projekt bei zwei Inputs (1) und (2) Kapitalkosten rentabel.
In einfachen Worten bedeuten Kapitalkosten, wie viel dieses Geld verdienen würde, wenn es nur in der Bank belassen würde. Sie verwenden die Kapitalkosten, um Barwerte auf den Barwert abzurechnen, dh 100 Dollar in fünf Jahren könnten heute 80 Dollar sein.
Im ersten Jahr sind 45 Millionen US-Dollar als Kapital zur Finanzierung des Projekts vorgesehen. Die Wirtschaftsprüfer haben festgestellt, dass ihre Kapitalkosten sechs Prozent betragen.
Sobald sie mit dem Abbau beginnen, beginnt das Geld zu kommen, sobald das Unternehmen das von ihm produzierte Kupfer findet und verkauft. Je mehr sie abbauen, desto mehr Geld verdienen sie, und ihre Prognose zeigt, dass ihr Cashflow ansteigt, bis sie 9 Millionen US-Dollar pro Jahr erreicht.
Nach 13 Jahren liegt der Kapitalwert bei 3.945.074 USD, sodass das Projekt rentabel ist. Laut Finanzanalysten beträgt die „Amortisationszeit“ 13 Jahre.
Erstellen einer Pivot-Tabelle
Eine "Pivot-Tabelle" ist im Wesentlichen ein Bericht. Wir nennen sie Pivot-Tabellen, da Sie sie einfach von einem Berichtstyp zu einem anderen wechseln können, ohne einen gesamten neuen Bericht erstellen zu müssen. So dass sie schwenken an Ort und Stelle. Lassen Sie uns ein grundlegendes Beispiel zeigen, in dem die grundlegenden Konzepte vermittelt werden.
Beispiel: Verkaufsberichte
Vertriebsmitarbeiter sind sehr wettbewerbsfähig (das gehört zu einem Verkäufer). Daher möchten sie natürlich wissen, wie sie sich am Ende des Quartals und Ende des Jahres gegeneinander antreten und wie hoch ihre Provisionen sein werden.
Angenommen, wir haben drei Verkäufer - Carlos, Fred und Julie -, die alle Erdöl verkaufen. Ihr Umsatz in US-Dollar pro Geschäftsquartal für das Jahr 2014 ist in der folgenden Tabelle dargestellt.
Um diese Berichte zu generieren, erstellen wir eine Pivot-Tabelle:
Wählen Sie "Einfügen -> Pivot-Tabelle", sie befindet sich auf der linken Seite der Symbolleiste:
Wählen Sie alle Zeilen und Spalten (einschließlich Name des Verkäufers) wie folgt aus:
Das Dialogfeld Pivot-Tabelle wird auf der rechten Seite der Kalkulationstabelle angezeigt.
Wenn wir im Dialogfeld der Pivot-Tabelle auf alle vier Felder klicken (Quartal, Jahr, Vertrieb und Verkäufer), fügt Excel der Tabelle einen Bericht hinzu, der keinen Sinn macht, aber warum?
Wie Sie sehen, haben wir alle vier Felder ausgewählt, die Sie dem Bericht hinzufügen möchten. Das Standardverhalten von Excel besteht darin, Zeilen nach Textfeldern zu gruppieren und dann die restlichen Zeilen zu summieren.
Hier gibt es die Summe des Jahres 2014 + 2014 + 2014 + 2014 = 24.168, was Unsinn ist. Außerdem wurde die Summe der Quartale 1 + 2 + 3 + 4 = 10 * 3 = 3 0 angegeben. Wir benötigen diese Informationen nicht. Daher deaktivieren wir diese Felder, um sie aus unserer Pivot-Tabelle zu entfernen.
Die "Umsatzsumme" (Gesamtumsatz) ist jedoch relevant, also werden wir das beheben.
Beispiel: Verkauf durch Verkäufer
Sie können die "Summe der Verkäufe" so bearbeiten, dass "Gesamtverkäufe" angezeigt wird. Dies ist übersichtlicher. Sie können die Zellen auch als Währung formatieren, genauso wie Sie auch andere Zellen formatieren würden. Klicken Sie zuerst auf „Umsatz“ und wählen Sie „Wertfeldeinstellungen“.
Im daraufhin angezeigten Dialog ändern wir den Namen in "Gesamtumsatz", klicken dann auf "Zahlenformat" und ändern ihn in "Währung".
Sie können dann Ihre Handarbeit in der Pivot-Tabelle sehen:
Beispiel: Verkäufe nach Verkäufer und Quartal
Nun fügen wir für jedes Quartal Zwischensummen hinzu. Um Zwischensummen hinzuzufügen, klicken Sie einfach mit der linken Maustaste auf das Feld „Quartal“ und halten Sie es gedrückt, und ziehen Sie es in den Bereich „Zeilen“. Sie können das Ergebnis auf dem folgenden Screenshot sehen:
Wenn wir gerade dabei sind, entfernen wir die "Sum of Quarter" -Werte. Klicken Sie einfach auf den Pfeil und klicken Sie auf "Feld entfernen". Im Screenshot können Sie jetzt sehen, dass wir die "Viertel" -Zeilen hinzugefügt haben, in denen die Umsätze der einzelnen Vertriebsmitarbeiter nach Quartalen aufgegliedert werden.
Mit diesen neuen Fähigkeiten können Sie nun aus Ihren eigenen Daten Pivot-Tabellen erstellen!
Fazit
Zum Abschluss haben wir Ihnen einige der Funktionen der Formeln und Funktionen von Microsoft Excel gezeigt, mit denen Sie Microsoft Excel auf Ihre geschäftlichen, akademischen oder sonstigen Anforderungen anwenden können.
Wie Sie gesehen haben, ist Microsoft Excel ein enormes Produkt mit so vielen Funktionen, dass die meisten Benutzer, selbst fortgeschrittene Benutzer, nicht alle kennen. Einige Leute könnten sagen, das macht es kompliziert; Wir empfinden es als umfassender.
Durch viele Beispiele aus der Praxis haben wir Ihnen hoffentlich nicht nur die in Microsoft Excel verfügbaren Funktionen gezeigt, sondern auch etwas über Statistik, lineare Programmierung, das Erstellen von Diagrammen, die Verwendung von Zufallszahlen und andere Ideen, die Sie jetzt übernehmen können Verwenden Sie in Ihrer Schule oder wo Sie arbeiten.
Denken Sie daran, wenn Sie zurückkehren und die Klasse erneut besuchen möchten, können Sie mit Lektion 1 neu beginnen!
