Viele Ökonomen oder Finanziers verbringen einen erheblichen Teil ihrer Zeit damit, an einem Computer mit der Office-Anwendung MS Excel zu arbeiten. Dieses Programm verfügt über zahlreiche Funktionen zur Erstellung von Berichten, Datenanalysen, Informationsplänen, mathematischen Berechnungen und vielem mehr. Die Kenntnis der wichtigsten Excel-Formeln und die Kombination verschiedener Funktionen erleichtern die Lösung praktischer Probleme erheblich und reduzieren den Zeit- und Arbeitsaufwand.
INDEX- und MATCH-Funktionen
IF-Funktion kombiniert mit AND-Funktion
Kombination von SUM- und OFFSET-Funktionen
Funktionen SUMIF und COUNTIF
Funktion MODE.SNGL
INDEX- und MATCH-Funktionen
Bei Finanzberechnungen wird häufig eine Kombination der Funktionen INDEX und MATCH verwendet. Ihre gemeinsame Aktion ähnelt der Funktionsweise der VLOOKUP-Funktion, hat jedoch im Vergleich dazu viele Vorteile. Zunächst werden wir diese Funktionen separat betrachten.
INDEX-Funktion
Die INDEX-Funktion ermittelt den Wert eines Elements in einem Datenblock anhand der angegebenen Zeilen- und Spaltennummer. Im Allgemeinen sieht die Struktur so aus:
INDEX (array; row_number; [column_number]), wo
- array – Dies ist ein Zellenblock, in dem die Suche durchgeführt wird.
- row_number – Dies ist die laufende Nummer der Zeile, in der sich der Wert befindet. Dieser Parameter ist erforderlich, wenn die Spaltennummer nicht angegeben ist.
- column_number – Dies ist die Seriennummer der Spalte, in der sich der gewünschte Wert befindet. Wenn die Zeilennummer in der Formel nicht angegeben ist, ist dieser Parameter erforderlich.
Wenn eine Zeilennummer und eine Spaltennummer angegeben werden, gibt die Funktion den Wert der Zelle zurück, die sich am Schnittpunkt dieser Daten befindet.
Betrachten Sie ein Beispiel. Es gibt eine Tabelle mit einer Liste von Artikelnummern und deren Abmessungen. Um die Länge der Produktnummer 2 zu ermitteln, müssen Sie eine Formel des Formulars schreiben =INDEX(B2:D6;3;2)
In diesem Beispiel ist das Ergebnis der Funktion der Inhalt der Zelle am Schnittpunkt der dritten Zeile und der zweiten Spalte dieses Arrays.
Leider ist in den meisten Berechnungen die Zeilen- oder Spaltennummer unbekannt. Dann hilft die MATCH-Funktion.
MATCH-Funktion
Die Aktion der Funktion MATCH ähnelt der Aktion der Funktion INDEX, aber MATCH gibt nicht den Zellenwert zurück, sondern die Zellenposition im angegebenen Bereich. Im Allgemeinen sieht die Formel folgendermaßen aus:
MATCH (search_value; array; [match_type]), wo
- search_value – Das ist, was Sie finden müssen. Hierbei kann es sich nicht nur um einen Text- oder Zahlenwert handeln, sondern auch um einen logischen Wert sowie um eine Verknüpfung zu einer Zelle.
- array – Dies ist der Zellbereich, der angezeigt wird.
- match_type – Dies ist ein optionaler Parameter, der auf „1“, „0“ oder „-1“ eingestellt werden kann. Es teilt der Funktion mit, welcher Wert gefunden werden soll: genau oder ungefähr. Wenn die Daten im Array in aufsteigender Reihenfolge abnehmen, zeigt der Parameter „1“ an, dass der Maximalwert kleiner oder gleich dem gewünschten Wert gewählt werden muss. Der Parameter “-1” wird für ein absteigendes Array angegeben. In diesem Fall wählt die Funktion den Mindestwert, der größer oder gleich dem gewünschten Wert ist. Der Parameter “0” findet den ersten Wert, der dem gewünschten entspricht. Es ist dieser Wert des passenden Typs, der in der Kombination der Funktionen INDEX und MATCH verwendet wird.
Betrachten Sie ein Beispiel, um die Funktionsweise dieser Funktion zu veranschaulichen. In der obigen Tabelle finden wir in der Spalte „Produkt Nr.“, wie das Konto für Produkt Nr. 2 lautet. Dazu schreiben wir die Formel:=MATCH(2;B2:B6;0)
Warum muss ich die Position eines Elements in einer Tabelle kennen? Es stellt sich heraus, dass es sehr praktisch ist, diesen Wert als Argument für die INDEX-Funktion zu verwenden.
Kombination von INDEX- und MATCH-Funktionen
Wenn wir beide Funktionen analysieren, wird klar, dass die INDEX-Funktion nach Zellenwerten nach Zeilennummer und Spaltennummer sucht. Gleichzeitig findet die MATCH-Funktion Zeilennummern und Spaltennummern. Wenn Sie diese beiden Funktionen in derselben Formel verwenden, ermittelt MATCH die relative Position des gewünschten Werts und die INDEX-Funktion verwendet diese Werte und gibt den Inhalt der berechneten Zellen zurück.
Betrachten Sie ein Beispiel. In der obigen Tabelle finden wir die Breite des Produkts bei Nummer 3. Hierzu verwenden wir die folgende Formel: =INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Es wird empfohlen, für diese Formeln absolute Links zu verwenden, damit die Suchbereiche beim Kopieren von Formeln nicht verwechselt werden.
Warum ist es besser, eine Kombination aus INDEX- und MATCH-Funktionen zu verwenden, als die VLOOKUP-Funktion zu verwenden? Erstens können Sie mit einer auf INDEX und MATCH basierenden Formel im angegebenen Bereich von links nach rechts und von rechts nach links nach den gewünschten Daten suchen. Bei Verwendung von VLOOKUP sollte sich der gewünschte Wert immer in der linken Spalte des Bereichs befinden.
Zweitens können Sie bei Verwendung der Funktion VLOOKUP der Tabelle keine Spalten hinzufügen oder löschen. Andernfalls ist das Ergebnis der Formel falsch. Dies liegt daran, dass die Syntax dieser Funktion die Angabe des gesamten Bereichs und der spezifischen Spaltennummer umfasst, aus der die Daten entnommen werden. Bei Verwendung der Funktionen INDEX und MATCH können Sie beliebig viele Spalten löschen oder hinzufügen.
Drittens gibt es bei Verwendung einer Kombination von INDEX- und MATCH-Funktionen keine Einschränkung hinsichtlich der Größe des Suchwerts, während die VLOOKUP-Funktion die Anzahl der Zeichen des Suchwerts auf 255 Zeichen begrenzt.
Viertens reduziert die Verwendung der Funktionen INDEX und MATCH beim Durchführen von Berechnungen in großen Datenfeldern die für die Suche nach Werten erforderliche Zeit im Vergleich zur Funktion VLOOKUP erheblich. Dies liegt daran, dass die VLOOKUP-Funktion für jeden Wert aus dem angegebenen Datenbereich aufgerufen wird. Im Gegensatz dazu führt eine Formel, die auf den Funktionen INDEX und MATCH basiert, einfach eine Suche durch und gibt ein Ergebnis zurück.
IF-Funktion kombiniert mit AND-Funktion
Die IF-Boolesche Funktion überprüft, ob der Inhalt der Zellen bestimmte Bedingungen erfüllt. Wenn es übereinstimmt, gibt die Funktion einen der benutzerdefinierten Werte zurück. Im Falle einer Abweichung wird ein anderer festgelegter Wert zurückgegeben. Die Syntax der Funktion lautet wie folgt:
=IF(logischer_Ausdruck; value_if_true; value_if_false), wo
- logischer_Ausdruck – Dies sind die Daten, die überprüft werden müssen, und die Bedingungen für die Überprüfung. Zum Beispiel А2>10.
- value_if_true – Dies ist der Eintrag, der angezeigt wird, wenn der Zellenwert die angegebene Bedingung erfüllt.
- value_if_false – Dies ist der Datensatz, der angezeigt wird, wenn der Zellenwert die angegebene Bedingung nicht erfüllt.
Viele Benutzer, die komplexe Finanzberechnungen durchgeführt haben, wissen, wie schwierig es ist, Formeln zu verstehen, die verschachtelte Schleifen mit der IF-Anweisung verwenden. Es stellt sich heraus, dass diese Formeln vereinfacht werden können, wenn Sie die IF-Funktion in Kombination mit den AND / OR-Funktionen verwenden. Die Kombination der AND- und IF-Funktionen funktioniert wie folgt. Wenn A = 1 und A = 2, gibt die Formel den Wert B zurück, andernfalls C. Bei der OR-Funktion funktioniert die Formel nicht so. Wenn A = 1 oder A = 2, gibt die Formel den Wert B zurück, andernfalls – den Wert C.
Betrachten Sie ein Beispiel. Erstellen Sie eine Formel, die den Inhalt der Zelle C2 auf 110 überprüft. Wenn die Zahl im Bereich von 90 bis 300 liegt, ist das Ergebnis 1, andernfalls 0. Die Formel lautet wie folgt: =IF(AND(C2>=C4;C2<=C5);C7;C8)
Wie aus der Abbildung hervorgeht, wird mit dem Ergebnis der Funktion ein Wert von 1 in die Zelle gelegt, die Zahl 110 liegt tatsächlich im Bereich von 90 bis 300.
Der Gesamtwert kann nicht nur eine Zahl sein, sondern auch ein Text, z. B. die Wörter “JA” oder “Nein” oder eine beliebige andere Phrase.
Kombination von SUM- und OFFSET-Funktionen
Die OFFSET-Funktion selbst wird selten verwendet, aber die Kombination mit anderen Funktionen kann zu sehr guten Ergebnissen führen. Die kombinierte Verwendung der Funktionen SUM und OFFSET ermöglicht es Ihnen beispielsweise, ziemlich komplexe Formeln zu erstellen, wenn Sie eine dynamische Funktion erstellen, die eine variable Anzahl von Zellen summiert. Um dieses Problem zu lösen, wird die SUM-Funktion verwendet, und anstelle der letzten Zelle wird die OFFSET-Funktion angegeben, dh, die Formel wird dynamisch.
Die resultierende Formel sieht folgendermaßen aus:
= SUM (Bereichsstart:OFFSET(Referenz,Zeilennummer,Spaltennummer)), wo
- Bereichsstart – Dies ist der Startpunkt des von der SUM-Funktion verwendeten Zellbereichs..
- Referenz – Dies ist eine Referenz auf die Zelle, mit der der Endpunkt des Bereichs berechnet wird.
- Zeilennummer – Dies ist die Anzahl der Zeilen, die zur Berechnung des Zellenversatzes verwendet werden. Dieser Wert kann positiv, negativ und gleich Null sein.
- Spaltennummer – Dies ist die Anzahl der Spalten rechts oder links von der angegebenen Zellreferenz. Wird bei der Berechnung des Offsets verwendet. Nach links verschoben ist dieser Wert negativ. Nach rechts verschoben ist der Wert positiv. Befinden sich die berechneten Daten in derselben Spalte, ist dieser Parameter Null.
Betrachten Sie ein Beispiel. Es gibt eine Tabelle mit den Seriennummern der Tage des Monats und den an jedem Tag erzielten Einnahmen. Die Informationen in der Tabelle werden täglich aktualisiert, indem eine Zeile mit dem pro Tag erzielten Einkommen hinzugefügt wird. Wir stellen die Formel in der letzten Zelle nach dem vierten Verkaufstag zusammen: =SUM(B2:OFFSET(B6;-1;0))
Um Informationen zum fünften Verkaufstag hinzuzufügen, müssen Sie nach dem vierten Tag eine leere Zeile einfügen und die erforderlichen Informationen eingeben. In diesem Fall hat die Formel die Form: =SUM(B2:OFFSET(B7;-1;0)). Das Gesamteinkommen erhöht sich um den Betrag des Einkommens, das am fünften Tag eingeht.
Funktionen SUMIF und COUNTIF
Diese beiden Funktionen werden sehr häufig in Finanzberechnungen verwendet. SUMIF ermittelt die Summe in einem bestimmten Bereich von Zellen unter einer bestimmten Bedingung. COUNTIF zählt alle Zellen, die der angegebenen Bedingung entsprechen.
Die SUMIF-Funktion hat folgende Struktur:
SUMIF(Reichweite;Kriterium; sum_range), wo
- Reichweite – Dies ist ein Array von Zellen, in denen die Einhaltung der angegebenen Kriterien überprüft wird.
- Kriterium – Dies ist eine Bedingung für die Auswahl von Zellen. Dies kann eine Zahl, ein Text, ein Ausdruck oder eine Zellreferenz sein.
- sum_range – Dies ist ein optionaler Parameter. Wenn Sie es nicht angeben, wird die Summierung unter Berücksichtigung des Arguments ” Reichweite ” durchgeführt.
Die Wirkung dieser Funktion ist anhand eines Beispiels leicht verständlich. Es gibt eine Tabelle mit einer Liste der Waren und deren Menge. Sie müssen die Menge von “Product 1″ finden. Die Formel für die Berechnung lautet wie folgt: =SUMIF(B2:B7;”Product 1”;C2:C7)
Im gleichen Beispiel können Sie die Anzahl der Waren ohne “Product 1″ mit der folgenden Formel berechnen: =SUMIF(B2:B7;”<>Product 1”;C2:C7)
Die COUNTIF-Funktion hat folgende Struktur:
COUNTIF (Reichweite;Kriterium), wo
Reichweite und Kriterium ähneln der SUMIF-Funktion.
Im selben Beispiel zählen wir die Anzahl der Zeilen “Product 1”. Die Formel für die Berechnung wird so aussehen : =COUNTIF(B2:B7;”Product 1″).
Mit dieser Formel können Sie die Anzahl der Zeilen berechnen, die bestimmte Bedingungen erfüllen. Um beispielsweise die Anzahl der Zeilen in dieser Tabelle mit der Anzahl der Produkte größer als 10 zu berechnen, müssen Sie die folgende Formel erstellen: =COUNTIF(C2:C7;”>10″).
Funktion MODE.SNGL
Die statistische Funktion MODE.SNGL und ihre veraltete Version MODE finden den am häufigsten vorkommenden Wert im Datenbereich (Array) und geben diesen Wert zurück. Funktionssyntax: =MODE((Nummer 1;[ Nummer 2];…), wo
- Nummer 1– Dies ist ein erforderliches Argument, bei dem es sich um eine Zahl, einen Verweis auf eine Zahlenzelle, ein Array oder einen Zellbereich handelt.
- Nummer 2 – optionales Argument. Solche Argumente können zwischen 1 und 255 liegen.
Argumente, die nicht in Zahlen konvertiert werden können, verursachen einen Formelfehler. Wenn es im angegebenen Bereich keine identischen Zahlen gibt, ist das Ergebnis der Funktion der Fehlerwert #N/A.
Warum kann ich diese Funktion für Finanzberechnungen verwenden? Zum Beispiel, um anhand von Übersichtsdaten herauszufinden, welche Produkte am häufigsten gekauft werden. Das Auswahlkriterium kann der Preis des Produkts, die Größe, das Volumen, die Abmessungen usw. sein. Zur Veranschaulichung betrachten wir eine Tabelle, die den Verkauf von Waren mit dem Verkaufsdatum, der Größe des Produkts und dem Preis wiedergibt. Um herauszufinden, welche Produktgrößen am häufigsten verkauft werden, verwenden wir die Formel: =MODE(C2:C6)
Die Abbildung zeigt, dass Produkte mit einer Größe von 36 am meisten nachgefragt werden. Somit bestimmt die MODE-Funktion das am häufigsten auftretende Ereignis im Ereignisspektrum.
Die Verwendung der Finanzfunktionen der Excel Office-Anwendung erleichtert somit die Durchführung von finanziellen und kommerziellen Abrechnungen für Kredite und Kredite, Finanz- und Investitionsanalysen sowie die Arbeit mit Wertpapieren erheblich. Ein wesentlicher Vorteil der Anwendung ist die Möglichkeit, ein Kennwort zum Schutz wichtiger Dokumente festzulegen. Dies verhindert, dass unbefugte Dritte auf Finanz- und Wirtschaftsberichte zugreifen können. Leider gehen manchmal Passwörter verloren, aber die Excel Passwort Knacken ist mit speziellen Programmen möglich.