Die Wirksamkeit großer, komplexer Operationen kann in der Regel nicht mit einem Indikator W charakterisiert werden, sondern muss durch andere, zusätzliche W 1, W 2,..., W unterstützt werden; Bei manchen ist es wünschenswert, mehr zu tun, bei anderen weniger. Bei der Beurteilung der Leistungsfähigkeit eines Unternehmens müssen beispielsweise eine Reihe von Indikatoren berücksichtigt werden:

• volle Produktmenge

  Selbstkostenpreis usw.

Bei der Analyse eines Kampfeinsatzes müssen neben dem Hauptindikator, der mathematischen Erwartung des dem Feind zugefügten Schadens, eine Reihe weiterer Faktoren berücksichtigt werden:

eigene Verluste

Ausführungszeit der Operation,

Munitionsverbrauch usw.

Diese Vielzahl von Leistungsindikatoren, von denen es wünschenswert ist, einige zu maximieren und andere zu minimieren, ist charakteristisch für jedes komplexe Operations-Research-Problem. In diesem Fall lautet die richtige Formulierung „maximale Wirkung bei gegebenen Kosten erzielen“ oder „bei minimalen Kosten eine bestimmte Wirkung erzielen“. Im Allgemeinen gibt es keine Lösung, die einen Indikator W 1 in ein Maximum und gleichzeitig einen anderen Indikator W 2 in ein Maximum (oder Minimum) verwandeln würde; Darüber hinaus gibt es für mehrere Indikatoren keine solche Lösung. Aber auch bei mehreren Indikatoren kann eine quantitative Analyse der Wirksamkeit sinnvoll sein, denn Es ermöglicht Ihnen, es im Voraus zu verwerfen eindeutig irrational Lösungsoptionen, die den besten Optionen in jeder Hinsicht unterlegen sind.

Schauen wir uns ein Beispiel an. Lassen Sie uns den Kampfeinsatz Q analysieren, bewertet anhand von zwei Indikatoren:

W– Wahrscheinlichkeit, einen Kampfauftrag abzuschließen;

S- Kosten der ausgegebenen Mittel.

Es empfiehlt sich, den ersten Indikator auf Maximum und den zweiten auf Minimum zu stellen.

Angenommen, es gibt 20 verschiedene Lösungsmöglichkeiten x 1, x 2,…, x 20. Für jeden von ihnen sind die Werte beider Indikatoren W und S bekannt (siehe Abb. 1.1).

Die Abbildung zeigt, dass einige Lösungsoptionen sofort verworfen werden können. Welche Optionen sollten bei der Bewertung der Wirksamkeit anhand von zwei Indikatoren bevorzugt werden? Offensichtlich diejenigen, die gleichzeitig am rechten und unteren Rand der Region liegen (in Abb. 1.1 - gepunktete Linie). Das. Es stehen noch vier Optionen zur Verfügung: X 16, X 17, X 19, X 20. Von diesen ist X 16 das wirksamste, aber relativ teuer; X 20 ist am günstigsten, aber nicht so effektiv. Die Aufgabe des Entscheidungsträgers besteht darin, herauszufinden, welchen Preis wir für eine bestimmte Effizienzsteigerung zahlen können oder umgekehrt, welche Effizienz wir zu opfern bereit sind, um nicht zu große materielle Verluste zu erleiden.

Sx

X

X X X

X X X

X X

X X X

X X

X X

X

X X

Da eine umfassende Bewertung einer Operation anhand mehrerer Indikatoren gleichzeitig schwierig ist, werden in der Praxis mehrere Indikatoren zu einem verallgemeinerten Indikator zusammengefasst. Als Kriterium wird oft ein Bruch herangezogen; Geben Sie im Zähler die Indikatoren W 1 ,…, W ein , die erhöht werden sollen, und im Nenner - diejenigen, die verringert werden sollen:

U=
(4)

Ein allgemeiner Nachteil eines Kriteriums vom Typ (4) besteht darin, dass eine mangelnde Wirksamkeit eines Indikators immer durch einen anderen ausgeglichen werden kann (z. B. eine geringe Wahrscheinlichkeit, einen Kampfeinsatz zu erfüllen, aufgrund eines geringen Munitionsverbrauchs usw.).

Bei zusammengesetzten Kriterien wird häufig davon ausgegangen, dass sie eine „gewichtete Summe“ einzelner Leistungsindikatoren sind:

U=α
+α
+…+α
(5)

wobei α positive oder negative Koeffizienten sind.

Positive Indikatoren werden für diejenigen Indikatoren festgelegt, deren Maximierung wünschenswert ist; negativ – für diejenigen, die minimiert werden sollten. Die absoluten Werte der Koeffizienten entsprechen dem Wichtigkeitsgrad der Indikatoren. Ein Kriterium vom Typ (5) hat den gleichen Nachteil (die Möglichkeit der gegenseitigen Kompensation heterogener Indikatoren) und kann zu falschen Empfehlungen führen. In Fällen, in denen α i jedoch nicht willkürlich gewählt werden, sondern so gewählt werden, dass das zusammengesetzte Kriterium das beste ist Wenn es seine Funktion erfüllt, ist es möglich, mit seiner Hilfe Ergebnisse von begrenztem Wert zu erzielen.

In manchen Fällen kann ein Problem mit mehreren Indikatoren auf ein Problem mit einem Indikator reduziert werden, wenn man einen (Haupt-)Effizienzindikator W 1 herausgreift und anstrebt, ihn zu maximieren, und den verbleibenden Hilfsindikatoren W nur ​​einige Einschränkungen dieser Form auferlegt 2, W 3,...

W
; …W
; W
; ...W

Diese Einschränkungen werden dann in den Komplex der festgelegten Bedingungen a 1 , a 2 ,… aufgenommen.

Bei dieser Problemstellung werden alle Leistungsindikatoren bis auf einen, den Hauptindikator, in die Kategorie übertragen gegebene Betriebsbedingungen. Lösungsmöglichkeiten, die nicht in die vorgegebenen Grenzen passen, werden sofort verworfen. Die erhaltenen Empfehlungen hängen natürlich davon ab, wie die Grenzwerte für die unterstützenden Indikatoren gewählt werden. Um festzustellen, wie stark dies die endgültigen Empfehlungen zur Auswahl einer Lösung beeinflusst, variieren Sie die Einschränkungen innerhalb angemessener Grenzen.

Es gibt eine andere Möglichkeit, eine Kompromisslösung zu finden, die man nennen kann: „ Methode der aufeinanderfolgenden Zugeständnisse" Ordnen Sie die Leistungsindikatoren nach abnehmender Bedeutung an: zuerst das Haupt-W 1, dann die anderen Hilfsindikatoren: W 2, W 3,…. Der Einfachheit halber gehen wir davon aus, dass jeder von ihnen auf ein Maximum gedreht werden muss (wenn dies nicht der Fall ist, reicht es aus, das Vorzeichen des Exponenten zu ändern). Das Verfahren zur Erstellung einer Kompromisslösung ist wie folgt. Zunächst wird nach einer Lösung gesucht, die den Haupteffizienzindikator W 1 maximiert. Basierend auf praktischen Überlegungen und der Genauigkeit, mit der die Ausgangsdaten bekannt sind (oft gering), wird dann ein gewisses „Zugeständnis“ ΔW 1 zugewiesen, das wir zulassen, um den zweiten Indikator W 2 zu maximieren. Wir begrenzen den Indikator W 1 so, dass er nicht kleiner als W 1 * - ∆W 1 ist (W 1 * ist der maximal mögliche Wert von W 1), und mit dieser Einschränkung suchen wir nach einer Lösung, die W 2 umdreht auf ein Maximum.

Als nächstes wird im W 2-Indikator erneut ein „Zugeständnis“ zugewiesen, auf dessen Kosten W 3 maximiert werden kann usw. Diese Methode ist gut, weil sofort klar ist, zu welchem ​​Preis „Zugeständnisse“ bei einem Indikator bei einem anderen erzielt werden. Gleichzeitig kann sich die Freiheit der Lösungswahl, die mit nur geringfügigen Zugeständnissen erkauft wird, als erheblich erweisen, denn Im Bereich des Maximums ändert sich die Effizienz der Lösung meist nur wenig.

Auf die eine oder andere Weise bleibt bei jeder Formalisierungsmethode die Aufgabe der quantitativen Begründung einer Entscheidung auf der Grundlage mehrerer Indikatoren nicht vollständig definiert, und die endgültige Wahl der Lösung wird durch den Willensakt des „Kommandanten“ bestimmt. Die Aufgabe des Forschers besteht darin, dem „Kommandanten“ eine ausreichende Datenmenge zur Verfügung zu stellen, die es ihm ermöglicht, die Vor- und Nachteile jeder Lösungsoption umfassend einzuschätzen und darauf basierend die endgültige Entscheidung zu treffen.

Im zweiten Teil unseres Funktions-Tutorials SVERWEIS(VLOOKUP) in Excel werden wir uns einige Beispiele ansehen, die Ihnen dabei helfen werden, Ihre ganze Kraft zu bündeln SVERWEIS um die anspruchsvollsten Excel-Aufgaben zu lösen. Bei den Beispielen wird davon ausgegangen, dass Sie bereits über grundlegende Kenntnisse zur Funktionsweise dieser Funktion verfügen. Wenn nicht, könnte es für Sie interessant sein, mit dem ersten Teil dieses Tutorials zu beginnen, in dem die Syntax und die grundlegende Verwendung erklärt werden SVERWEIS. Nun, fangen wir an.

Suchen Sie in Excel nach mehreren Kriterien

Funktion SVERWEIS Excel ist ein wirklich leistungsstarkes Tool zum Suchen nach einem bestimmten Wert in einer Datenbank. Es gibt jedoch eine erhebliche Einschränkung: Die Syntax ermöglicht die Suche nur nach einem Wert. Was ist, wenn Sie nach mehreren Bedingungen suchen müssen? Die Lösung finden Sie weiter unten.

Beispiel 1: Suche nach 2 verschiedenen Kriterien

Nehmen wir an, wir haben eine Liste mit Bestellungen und möchten diese finden Menge der Ware(Menge), basierend auf zwei Kriterien – Kundenname(Kunde) und Der Name des Produkts(Produkt). Erschwerend kommt hinzu, dass jeder der Käufer mehrere Arten von Waren bestellt hat, wie aus der folgenden Tabelle hervorgeht:

Regelmäßige Funktion SVERWEIS funktioniert in diesem Szenario nicht, da der erste gefundene Wert zurückgegeben wird, der mit dem angegebenen Suchwert übereinstimmt. Wenn Sie beispielsweise die Menge eines Artikels wissen möchten Süßigkeiten, vom Käufer bestellt Jeremy Hill, schreiben Sie die folgende Formel auf:

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

– Diese Formel gibt das Ergebnis zurück 15 , entsprechend dem Produkt Äpfel, da es der erste Wert ist, der übereinstimmt.

Es gibt eine einfache Lösung: Erstellen Sie eine zusätzliche Spalte, in der alle erforderlichen Kriterien zusammengefasst werden. In unserem Beispiel sind das die Spalten Kundenname(Kunde) und Der Name des Produkts(Produkt). Denken Sie daran, dass die zusammengeführte Spalte immer die Spalte ganz links im Suchbereich sein muss, da es sich um die linke Spalte handelt, die die Funktion ausführt SVERWEIS sucht bei der Suche nach einem Wert.

Sie fügen der Tabelle also eine Hilfsspalte hinzu und kopieren eine Formel wie diese in alle Zellen: =B2&C2. Wenn Sie möchten, dass die Zeichenfolge besser lesbar ist, können Sie die verketteten Werte durch ein Leerzeichen trennen: =B2&“ „&C2. Danach können Sie die folgende Formel verwenden:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=VLOOKUP("Jeremy Hill Sweets";$A$7:$D$18;4;FALSE)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

Wo ist die Zelle? B1 enthält den verketteten Wert des Arguments Lookup-Wert(Suchwert) und 4 - Streit col_index_num(Spaltennummer), d.h. Die Nummer der Spalte, die die abzurufenden Daten enthält.

Beispiel 2: VLOOKUP basierend auf zwei Kriterien, wobei die Tabelle auf einem anderen Blatt angezeigt wird

Wenn Sie die Haupttabelle aktualisieren müssen, indem Sie Daten aus einer zweiten Tabelle (Nachschlagetabelle) hinzufügen, die sich auf einem anderen Blatt oder in einer anderen Excel-Arbeitsmappe befindet, können Sie den erforderlichen Wert direkt in der Formel erfassen, die Sie in die Haupttabelle einfügen .

Wie im vorherigen Beispiel benötigen Sie in der Lookup-Tabelle eine Hilfsspalte mit den kombinierten Werten. Diese Spalte muss die Spalte ganz links im für die Suche angegebenen Bereich sein.

Also die Formel mit SVERWEIS könnte so sein:

VLOOKUP(B2&" "&C2,Bestellungen!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "Bestellungen!$A&$2:$D$2;4;FALSE)

Hier enthalten die Spalten B und C Kundennamen bzw. Produktnamen und einen Link Bestellungen!$A&$2:$D$2 Definiert eine Tabelle, die in einem anderen Arbeitsblatt nachgeschlagen werden soll.

Um die Formel besser lesbar zu machen, können Sie dem angezeigten Bereich einen Namen geben, wodurch die Formel viel einfacher aussieht:

VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=VLOOKUP(B2&" "Orders;4;FALSE)

Damit die Formel funktioniert, müssen die Werte in der Spalte ganz links der angezeigten Tabelle auf die gleiche Weise wie in den Suchkriterien kombiniert werden. In der Abbildung oben haben wir die Werte kombiniert und ein Leerzeichen dazwischen gesetzt. Dasselbe muss im ersten Argument der Funktion (B2&“ „&C2“) erfolgen.

Erinnern! Funktion SVERWEIS Da der Wert auf 255 Zeichen begrenzt ist, kann nicht nach einem Wert gesucht werden, der länger als 255 Zeichen ist. Beachten Sie dies und stellen Sie sicher, dass die Länge des gesuchten Werts diese Grenze nicht überschreitet.

Ich stimme zu, dass das Hinzufügen einer Hilfsspalte nicht die eleganteste und nicht immer akzeptable Lösung ist. Sie können das Gleiche auch ohne die Hilfsspalte tun, aber das würde eine viel komplexere Formel mit einer Kombination von Funktionen erfordern INDEX(INDEX) und ÜBEREINSTIMMEN(ÜBEREINSTIMMEN).

Wir extrahieren den 2., 3. usw. Werte mit VLOOKUP

Das weißt du bereits SVERWEIS kann nur einen passenden Wert zurückgeben, bzw. den ersten gefundenen. Was aber, wenn dieser Wert im angezeigten Array mehrmals vorkommt und Sie den zweiten oder dritten Wert extrahieren möchten? Was wäre, wenn alle Werte? Das Problem scheint kompliziert, aber es gibt eine Lösung!

Angenommen, eine Spalte der Tabelle enthält die Namen der Kunden (Kundenname) und die andere enthält die Produkte (Produkt), die sie gekauft haben. Versuchen wir, das 2., 3. und 4. Produkt zu finden, das ein bestimmter Kunde gekauft hat.

Der einfachste Weg besteht darin, vor der Spalte eine Hilfsspalte hinzuzufügen Kundenname und füllen Sie es mit Kundennamen mit Wiederholungsnummer jedes Namens, z. B. John Doe1, John Doe2 usw. Machen wir den Trick mit der Nummerierung mithilfe der Funktion ZÄHLENWENN(COUNTIF), vorausgesetzt, die Kundennamen befinden sich in Spalte B:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

Danach können Sie die normale Funktion nutzen SVERWEIS um die Bestellung zu finden, die Sie benötigen. Zum Beispiel:

• Wir finden 2 Dan Brown:

  VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

• Wir finden 3 vom Käufer bestellte Ware Dan Brown:

  VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

Wenn Sie nur suchen 2 Um dies zu wiederholen, können Sie dies auch ohne die Hilfsspalte tun, indem Sie eine komplexere Formel erstellen:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE),"")
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,TRUE)"")

In dieser Formel:

• $F$2– eine Zelle mit dem Namen des Käufers (er bleibt unverändert, bitte beachten Sie – der Link ist absolut);
• $B$- Spalte Kundenname;
• Tabelle 4– Ihr Tisch (an diesem Ort kann es auch einen Stammtisch geben);
• $C16– die Endzelle Ihrer Tabelle oder Ihres Bereichs.

Diese Formel findet nur den zweiten passenden Wert. Wenn Sie die verbleibenden Wiederholungen extrahieren müssen, verwenden Sie die vorherige Lösung.

Wenn Sie eine Liste aller Übereinstimmungen benötigen, verwenden Sie die Funktion SVERWEIS Hier gibt es keine Hilfe, da jeweils nur ein Wert zurückgegeben wird – das war’s. Aber Excel hat eine Funktion INDEX(INDEX), der diese Aufgabe problemlos bewältigen kann. Wie eine solche Formel aussehen wird, erfahren Sie im folgenden Beispiel.

Alle Wiederholungen des gewünschten Wertes abrufen

Wie oben erwähnt, SVERWEIS Es können nicht alle doppelten Werte aus dem angezeigten Bereich extrahiert werden. Dazu benötigen Sie eine etwas komplexere Formel, die aus mehreren Excel-Funktionen besteht, wie z INDEX(INDEX), KLEIN(KLEIN) und REIHE(LINIE)

Die folgende Formel findet beispielsweise alle Vorkommen eines Werts aus Zelle F2 im Bereich B2:B16 und gibt das Ergebnis aus denselben Zeilen in Spalte C zurück.

(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"");ROW()-3));"") )

Geben Sie diese Matrixformel in mehrere benachbarte Zellen ein, z. B. F4:F8, wie in der Abbildung unten gezeigt. Die Anzahl der Zellen muss gleich oder größer als die maximal mögliche Anzahl von Wiederholungen des Suchwerts sein. Vergessen Sie nicht zu klicken Strg+Umschalt+Eingabetaste um die Matrixformel korrekt einzugeben.

Wenn Sie verstehen möchten, wie es funktioniert, schauen wir uns die Details der Formel etwas genauer an:

Teil 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")
IF($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16– Vergleichen Sie den Wert in Zelle F2 mit jedem der Werte im Bereich B2:B16. Wenn eine Übereinstimmung gefunden wird, dann der Ausdruck REIHE(C2:C16)-1 gibt die Nummer der entsprechenden Zeile (Wert) zurück -1 ermöglicht es Ihnen, die Kopfzeile wegzulassen). Wenn es keine Übereinstimmungen gibt, wird die Funktion WENN(IF) gibt eine leere Zeichenfolge zurück.

Das Ergebnis der Funktion WENN(WENN) es ein horizontales Array wie dieses geben wird: (1,"",3,"",5,"","",,"",,"",,"",,"",12," ","",,"" )

Teil 2:

ROW()-3
ROW()-3

Hier ist die Funktion REIHE(ROW) fungiert als zusätzlicher Zähler. Da die Formel in die Zellen F4:F9 kopiert wird, subtrahieren wir die Zahl 3 aus dem Funktionsergebnis, um den Wert zu erhalten 1 in einer Zelle F4(Zeile 4, subtrahiere 3), um zu erhalten 2 in einer Zelle F5(Zeile 5, subtrahiere 3) und so weiter.

Teil 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

Funktion KLEIN(KLEIN) kehrt zurück nth der kleinste Wert im Datenarray. In unserem Fall wird durch die Funktion bestimmt, welche Position (von der kleinsten) zurückgegeben werden soll REIHE(LINE) (siehe Teil 2). Also für die Zelle F4 Funktion KLEIN((array);1) kehrt zurück 1(kleinstes) Element des Arrays, das heißt 1 . Für Zelle F5 kehrt zurück 2 das kleinste Element des Arrays 3 , usw.

Teil 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;""),ROW()-3))

Funktion INDEX(INDEX) gibt einfach den Wert einer bestimmten Zelle in einem Array zurück C2:C16. Für Zelle F4 Funktion INDEX($C$2:$C$16,1) wird zurückkehren Äpfel, Für F5 Funktion INDEX($C$2:$C$16,3) wird zurückkehren Süßigkeiten usw.

Teil 5:

IFERROR()
IFERROR()

Zum Schluss fügen wir die Formel in die Funktion ein IFERROR(IFERROR), da Sie mit einer Fehlermeldung wahrscheinlich nicht zufrieden sein werden #N / A(#N/A), wenn die Anzahl der Zellen, in die die Formel kopiert wird, geringer ist als die Anzahl der doppelten Werte im angezeigten Bereich.

Zweidimensionale Suche nach bekannten Zeilen und Spalten

Bei der Durchführung einer zweidimensionalen Suche in Excel wird anhand einer bekannten Zeilen- und Spaltennummer nach einem Wert gesucht. Mit anderen Worten: Sie rufen den Zellenwert am Schnittpunkt einer bestimmten Zeile und Spalte ab.

Gehen wir also zu unserer Tabelle und schreiben die Formel mit der Funktion auf SVERWEIS Hier finden Sie Informationen zu den Kosten der im März verkauften Zitronen.

Es gibt mehrere Möglichkeiten, eine zweidimensionale Suche durchzuführen. Lernen Sie die verfügbaren Optionen kennen und wählen Sie diejenige aus, die am besten zu Ihnen passt.

VLOOKUP- und MATCH-Funktionen

Sie können eine Reihe von Funktionen nutzen SVERWEIS(SVERWEIS) und SUCHEN(MATCH), um den Wert am Schnittpunkt der Felder zu finden Der Name des Produkts(Zeichenfolge) und Monat(Spalte) des betreffenden Arrays:

VLOOKUP("Zitronen",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=VLOOKUP("Zitronen",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

Die obige Formel ist eine reguläre Funktion SVERWEIS, das nach einer genauen Übereinstimmung des Werts „Lemons“ in den Zellen A2 bis A9 sucht. Da Sie jedoch nicht wissen, in welcher Spalte sich die Verkäufe für März befinden, können Sie die Spaltennummer für das dritte Argument der Funktion nicht angeben SVERWEIS. Stattdessen wird die Funktion verwendet SUCHEN um diese Spalte zu definieren.

MATCH("Mar",$A$1:$I$1,0)
MATCH("Mar",$A$1:$I$1,0)

In die menschliche Sprache übersetzt bedeutet diese Formel:

• Wir suchen nach den Symbolen „Mar“ – Argument Lookup-Wert(Suchwert);
• Wir suchen in den Zellen von A1 bis I1 nach dem Argument lookup_array(view_array);
• Gibt ein exakt passendes Argument zurück match_type(match_type).

Benutzen 0 Im dritten Argument teilen Sie die Funktion mit SUCHEN Suchen Sie nach dem ersten Wert, der genau mit dem Suchwert übereinstimmt. Dies entspricht dem Wert FALSCH(FALSE) für das vierte Argument SVERWEIS.

So können Sie eine Formel für eine Zwei-Wege-Suche in Excel erstellen, die auch als zweidimensionale Suche oder Zwei-Wege-Suche bezeichnet wird.

SUMPRODUCT-Funktion

Funktion SUMMENPRODUKT(SUMPRODUCT) gibt die Summe der Produkte der ausgewählten Arrays zurück:

SUMPRODUCT(($A$2:$A$9="Zitronen")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="Zitronen")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX- und MATCH-Funktionen

Ich werde diese Funktionen im nächsten Artikel ausführlich erläutern. Sie können also zunächst einfach diese Formel kopieren:

INDEX($A$2:$I$9,MATCH("Zitronen",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=INDEX($A$2:$I$9,MATCH("Zitronen",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

Benannte Bereiche und der Schnittpunktoperator

Wenn Sie nicht gerade verrückt nach all diesen komplizierten Excel-Formeln sind, könnte Ihnen diese visuelle und einprägsame Art gefallen:

Wenn Sie einen Namen eingeben, zeigt Microsoft Excel eine QuickInfo mit einer Liste übereinstimmender Namen an, genau wie bei der Eingabe einer Formel.

1. Klicken Eingeben und überprüfen Sie das Ergebnis

Unabhängig davon, welche der oben genannten Methoden Sie wählen, ist das Ergebnis einer zweidimensionalen Suche im Allgemeinen dasselbe:

Wir verwenden mehrere VLOOKUPS in einer Formel

Es kommt vor, dass die Haupttabelle und die Nachschlagetabelle keine gemeinsame Spalte haben, was die Verwendung der üblichen Funktion verhindert SVERWEIS. Es gibt jedoch eine andere Tabelle, die nicht die für uns interessanten Informationen enthält, sondern eine gemeinsame Spalte mit der Haupttabelle und der Nachschlagetabelle hat.

Schauen wir uns das folgende Beispiel an. Wir haben eine Haupttabelle mit einer Spalte Artikelnummer (neu), wo Sie eine Spalte mit den entsprechenden Preisen aus einer anderen Tabelle hinzufügen müssen. Darüber hinaus haben wir 2 Nachschlagetabellen. Die erste (Nachschlagetabelle 1) enthält aktualisierte Zahlen Artikelnummer (neu) und Produktnamen und die zweite (Nachschlagetabelle 2) – Produktnamen und alte Nummern Artikelnummer (alt).

Um Preise aus der zweiten Nachschlagetabelle zur Haupttabelle hinzuzufügen, müssen Sie eine Aktion namens Double ausführen SVERWEIS oder verschachtelt SVERWEIS.

1. Schreiben Sie die Funktion SVERWEIS, das den Produktnamen in der Tabelle findet Nachschlagetabelle 1 verwenden Artikelnummer, als gewünschter Wert:

   VLOOKUP(A2,New_SKU,2,FALSE)
   =VLOOKUP(A2,New_SKU,2,FALSE)

   Hier Neue_SKU– benannter Bereich $A:$B in der Tabelle Nachschlagetabelle 1, A 2 – das ist Spalte B, die die Namen der Produkte enthält (siehe Bild oben)

2. Schreiben Sie eine Formel auf, um Preise aus einer Tabelle einzufügen Nachschlagetabelle 2 basierend auf bekannten Produktnamen. Fügen Sie dazu die zuvor erstellte Formel als Suchwert für die neue Funktion ein SVERWEIS:

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   Hier Preis– benannter Bereich $A:$C in der Tabelle Nachschlagetabelle 2, A 3 ist Spalte C, die Preise enthält.

Die folgende Abbildung zeigt das Ergebnis, das von der von uns erstellten Formel zurückgegeben wird:

Dynamische Ersetzung von Daten aus verschiedenen Tabellen mithilfe von VLOOKUP und INDIRECT

Lassen Sie uns zunächst klären, was wir unter dem Ausdruck „Dynamische Ersetzung von Daten aus verschiedenen Tabellen“ verstehen, um sicherzustellen, dass wir uns richtig verstehen.

Es gibt Situationen, in denen mehrere Blätter mit Daten desselben Formats vorhanden sind und es notwendig ist, die erforderlichen Informationen aus einem bestimmten Blatt zu extrahieren, abhängig von dem Wert, der in eine bestimmte Zelle eingegeben wird. Ich denke, es ist einfacher, dies anhand eines Beispiels zu erklären.

Stellen Sie sich vor, Sie hätten Verkaufsberichte für mehrere Regionen mit den gleichen Produkten und im gleichen Format. Sie müssen Verkaufszahlen für eine bestimmte Region ermitteln:

Wenn Sie nur zwei solcher Berichte haben, können Sie eine unglaublich einfache Formel mit Funktionen verwenden SVERWEIS Und WENN(IF), um den gewünschten Bericht für die Suche auszuwählen:

VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

• $D$2– Dies ist eine Zelle, die den Namen des Produkts enthält. Bitte beachten Sie, dass wir hier absolute Bezüge verwenden, um eine Änderung des Suchwerts beim Kopieren der Formel in andere Zellen zu vermeiden.
• $D3– Dies ist eine Zelle mit dem Namen der Region. Wir verwenden einen absoluten Bezug für die Spalte und einen relativen Bezug für die Zeile, da wir planen, die Formel in andere Zellen in derselben Spalte zu kopieren.
• FL_Sales Und CA_Sales– Namen von Tabellen (oder benannten Bereichen), die die entsprechenden Verkaufsberichte enthalten. Sie können natürlich normale Blattnamen und Zellbereichsreferenzen verwenden, z. B. „FL-Blatt“!$A$3:$B$10, aber benannte Bereiche sind viel praktischer.

Wenn es jedoch viele solcher Tabellen gibt, ist die Funktion WENN– Das ist nicht die beste Lösung. Stattdessen können Sie die Funktion verwenden INDIREKT(INDIREKT), um den gewünschten Suchbereich zurückzugeben.

Wie Sie wahrscheinlich wissen, ist die Funktion INDIREKT wird verwendet, um einen Link mit einer gegebenen Textzeichenfolge zurückzugeben, was genau das ist, was wir jetzt brauchen. Sie können den Ausdruck also gerne durch die Funktion in der obigen Formel ersetzen WENN zum Link mit der Funktion INDIREKT. Das ist die Kombination SVERWEIS Und INDIREKT Funktioniert hervorragend zu zweit:

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

• $D$2– Dies ist eine Zelle mit dem Namen des Produkts, diese bleibt aufgrund der absoluten Verlinkung unverändert.
• $D3– Dies ist die Zelle, die den ersten Teil des Regionsnamens enthält. In unserem Beispiel ist dies der Fall FL.
• _Verkäufe– der gemeinsame Teil des Namens aller benannten Bereiche oder Tabellen. In Kombination mit dem Wert in Zelle D3 ergibt sich der vollständige Name des gewünschten Bereichs. Nachfolgend einige Details für diejenigen, die noch keine Erfahrung mit der Funktion haben INDIREKT.

Wie INDIREKT und SVERWEIS funktionieren

Lassen Sie mich zunächst an die Funktionssyntax erinnern INDIREKT(INDIREKT):

• A1, wenn das Argument gleich ist WAHR(TRUE) oder nicht angegeben;
• R1C1, Wenn FALSE(LÜGE).

Kommen wir also zurück zu unseren Verkaufsberichten. Wie Sie sich erinnern, handelt es sich bei jedem Bericht um eine separate Tabelle, die sich auf einem separaten Blatt befindet. Damit die Formel ordnungsgemäß funktioniert, müssen Sie Ihren Tabellen (oder Bereichen) Namen geben und alle Namen müssen einen gemeinsamen Teil haben. Zum Beispiel so: CA_Sales, FL_Verkäufe, TX_Sales usw. Wie Sie sehen, enthalten alle Namen „_Sales“.

Funktion INDIREKT verbindet den Wert in Spalte D und die Textzeichenfolge „_Sales“ und meldet so SVERWEIS in welche Tabelle man schauen soll. Wenn der Wert „FL“ in Zelle D3 steht, durchsucht die Formel die Tabelle FL_Verkäufe, wenn „CA“ in der Tabelle steht CA_Sales usw.

Ergebnis der Funktionen SVERWEIS Und INDIREKT wird folgendes sein:

Wenn sich die Daten in verschiedenen Excel-Arbeitsmappen befinden, müssen Sie den Namen der Arbeitsmappe vor dem benannten Bereich hinzufügen, zum Beispiel:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

Wenn die Funktion INDIREKT auf eine andere Arbeitsmappe verweist, muss diese Arbeitsmappe geöffnet sein. Wenn es geschlossen ist, meldet die Funktion einen Fehler #REF!(#VERKNÜPFUNG!).

KPI ist ein Leistungsindikator, mit dem Sie die Wirksamkeit durchgeführter Maßnahmen objektiv beurteilen können. Dieses System dient der Auswertung verschiedener Indikatoren (Aktivitäten des Gesamtunternehmens, einzelner Strukturen, spezifischer Spezialisten). Es erfüllt nicht nur Kontrollfunktionen, sondern regt auch die Arbeitstätigkeit an. Oftmals wird ein Vergütungssystem auf Basis von KPIs aufgebaut. Hierbei handelt es sich um eine Methode zur Bildung des variablen Gehaltsanteils.

KPI-Leistungsindikatoren: Beispiele in Excel

Der stimulierende Faktor im KPI-Motivationssystem ist die monetäre Belohnung. Es kann von dem Mitarbeiter entgegengenommen werden, der die ihm übertragene Aufgabe erledigt hat. Die Höhe der Prämie/Bonus hängt von der Leistung eines bestimmten Mitarbeiters im Berichtszeitraum ab. Die Höhe der Vergütung kann festgesetzt oder als Prozentsatz des Gehalts ausgedrückt werden.

Jedes Unternehmen legt die wichtigsten Leistungsindikatoren und deren Gewichtung individuell fest. Die Daten hängen von den Unternehmenszielen ab. Zum Beispiel:

1. Ziel ist es, einen Produktverkaufsplan von 500.000 Rubel monatlich zu erreichen. Der Schlüsselindikator ist der Verkaufsplan. Messsystem: tatsächlicher Verkaufsbetrag / geplanter Verkaufsbetrag.
2. Ziel ist es, die Sendungsmenge in diesem Zeitraum um 20 % zu steigern. Der Schlüsselindikator ist die durchschnittliche Sendungsmenge. Messsystem: tatsächliche durchschnittliche Sendung / geplante durchschnittliche Sendung.
3. Ziel ist es, die Kundenzahl in einer bestimmten Region um 15 % zu steigern. Der Schlüsselindikator ist die Anzahl der Kunden in der Unternehmensdatenbank. Messsystem: tatsächliche Kundenzahl / geplante Kundenzahl.

Auch die Streuung der Koeffizienten (Gewichte) bestimmt das Unternehmen selbstständig. Zum Beispiel:

1. Eine Erfüllung des Plans zu weniger als 80 % ist nicht akzeptabel.
2. Planerfüllung 100 % - Koeffizient 0,45.
3. Erfüllung des Plans 100-115 % - Koeffizient 0,005 für alle 5 %.
4. Keine Fehler – Koeffizient 0,15.
5. Im Berichtszeitraum gab es keine Kommentare – Koeffizient 0,15.

Dies ist nur eine mögliche Option zur Bestimmung von Motivationskoeffizienten.

Der entscheidende Punkt bei der KPI-Messung ist das Verhältnis des tatsächlichen zum geplanten Indikator. Fast immer besteht das Gehalt eines Mitarbeiters aus einem Gehalt (fixer Teil) und einer Prämie (variabler/variabler Teil). Der Motivationskoeffizient beeinflusst die Bildung der Variable.

Nehmen wir an, dass das Verhältnis der konstanten und variablen Gehaltsanteile 50 × 50 beträgt. Wichtige Leistungsindikatoren und deren Gewichtung:

Nehmen wir die folgenden Koeffizientenwerte an (dasselbe gilt für Indikator 1 und Indikator 2):

KPI-Tabelle in Excel:

Erläuterungen:

Dies ist eine Beispiel-KPI-Tabelle in Excel. Jedes Unternehmen stellt sein eigenes Unternehmen zusammen (unter Berücksichtigung der Arbeitsmerkmale und des Bonussystems).

KPI-Matrix und Beispiel in Excel

Zur Bewertung der Mitarbeiter anhand wichtiger Leistungsindikatoren wird eine Matrix bzw. Zielvereinbarung erstellt. Die allgemeine Form sieht so aus:

1. Schlüsselindikatoren sind die Kriterien, nach denen die Arbeit des Personals beurteilt wird. Sie sind für jede Position unterschiedlich.
2. Gewichte sind Zahlen im Bereich von 0 bis 1, deren Gesamtsumme 1 beträgt. Sie spiegeln die Prioritäten der einzelnen Schlüsselindikatoren unter Berücksichtigung der Unternehmensziele wider.
3. Basis – akzeptabler Mindestwert des Indikators. Unterhalb des Grundniveaus – kein Ergebnis.
4. Norm – geplantes Niveau. Etwas, das ein Mitarbeiter tun muss. Unten - der Mitarbeiter ist seinen Pflichten nicht nachgekommen.
5. Ein Ziel ist ein Wert, den es anzustreben gilt. Ein überdurchschnittlicher Indikator, mit dem Sie Ihre Ergebnisse verbessern können.
6. Fakt – tatsächliche Arbeitsergebnisse.
7. Der KPI-Index zeigt das Ergebnisniveau im Verhältnis zur Norm.

Formel zur Berechnung des KPI:

KPI-Index = ((Istwert – Basis) / (Norm – Basis)) * 100 %.

Ein Beispiel für das Ausfüllen einer Matrix für einen Büroleiter:

Der Leistungskoeffizient ist die Summe der Produkte aus Indizes und Gewichten. Die Leistungsbewertungen der Mitarbeiter werden durch bedingte Formatierung übersichtlich dargestellt.

Angenommen, Sie möchten Werte mit mehr als einer Bedingung summieren, beispielsweise die Summe der Verkäufe eines Produkts in einer bestimmten Region. Dies ist eine gute Verwendung der SUMIFS-Funktion in einer Formel.

Beachten Sie dieses Beispiel, in dem wir zwei Bedingungen haben: erforderlicher Verkaufsbetrag Fleisch(aus Spalte C) bis Süd Flächen (aus Spalte a).

Hier ist eine Formel, die für acomplish verwendet werden kann:

= SUMIFS (D2: D11; a2: A11; „Süd“; C2: C11; „Fleisch“)

Das Ergebnis ist ein Wert von 14.719.

Schauen wir uns eine detailliertere Darstellung jedes Teils der Formel an.

SUMIFS ist eine arithmetische Formel. Berechnet die Zahlen, die in diesem Fall in Spalte D stehen. Der erste Schritt besteht darin, die Position der Zahlen anzugeben.

=SUMIFS(D2:D11,

Mit anderen Worten: Sie möchten, dass die Formel die Zahlen in dieser Spalte summiert, wenn sie bestimmte Bedingungen erfüllen. Dieser Zellbereich ist der erste Streit In dieser Formel das erste Datenelement, das die Funktion als Eingabe verwendet.

Dann müssen Sie die Daten finden, die die beiden Bedingungen erfüllen, und die erste Bedingung eingeben, indem Sie für die Funktion den Speicherort der Daten angeben (a2: A11) und auch, dass die Bedingung „Süd“ ist. Beachten Sie die Kommas zwischen den einzelnen Argumenten.

=SUMIFS(D2:D11,A2:A11,"South";

Die Anführungszeichen um das Wort „South“ weisen darauf hin, dass es sich um Textdaten handelt.

Schließlich geben Sie die Argumente für die zweite Bedingung ein – den Zellbereich (C2:C11), der das Wort „Fleisch“ enthält, sowie das Wort selbst (in Anführungszeichen eingeschlossen), damit Excel sie abgleichen kann. Vervollständigen Sie die Formel, indem Sie die schließende Klammer schließen ) , und drücken Sie dann die Eingabetaste. Das Ergebnis liegt erneut bei 14.719.

=SUMIFS(D2:D11,A2:A11,"Süd",C2:C11,"Fleisch")

Wenn Sie sich beim Eingeben der SUMIFS-Funktion in Excel nicht an diese Argumente erinnern, steht Ihnen die Hilfe zur Verfügung. Nach Eingabe der Formel = SUMIFEN ( Autovervollständigungsformeln werden unterhalb der Formel angezeigt und die Liste der Argumente wird in der erforderlichen Reihenfolge aufgelistet.

Schauen Sie sich das Bild der Formel-Autovervollständigung und der Argumentliste in unserem Beispiel an Summenbereich ist D2:D11, die Spalte der Zahlen, die summiert werden müssen. Kriterien_Bereich1- a2. A11 – Datenspalte mit Bedingung1"Süd".

Während Sie die Formel eingeben, werden die verbleibenden Argumente (hier nicht angezeigt) in Formula AutoComplete angezeigt. range_conditions2 ist der Bereich C2:C11, also die Datenspalte, die enthält Bedingung2- "Fleisch".

Wenn Sie klicken SUMIFS Wenn Sie eine Formel automatisch vervollständigen, wird ein Artikel geöffnet, in dem Sie weitere Informationen erhalten.

Versuchen Sie zu üben

Wenn Sie mit der SUMIFS-Funktion experimentieren möchten, finden Sie hier einige Beispieldaten und eine Formel, die die Funktion verwendet.

In dieser Excel für das Web-Arbeitsmappe können Sie direkt mit Beispieldaten und Formeln arbeiten. Ändern Sie Werte und Formeln oder fügen Sie eigene hinzu, um die sofortige Änderung der Ergebnisse zu sehen.

Kopieren Sie alle Zellen aus der Tabelle unten und fügen Sie sie in Zelle A1 einer neuen Excel-Tabelle ein. Sie können die Breite der Spalten anpassen, um die Formeln besser darzustellen.

VPR basierend auf zwei oder mehr Kriterien

Sicherlich weiß jeder, der mit der VLOOKUP-Funktion vertraut ist, dass sie ausschließlich in der linken Spalte der angegebenen Tabelle nach bestimmten Werten sucht (mehr über VLOOKUP können Sie im Artikel lesen: So finden Sie einen Wert in einer anderen Tabelle oder VLOOKUP-Stärke) . Viele Menschen wissen auch, dass VLOOKUP nur auf der Grundlage eines Werts sucht.

Hat der Artikel geholfen? Teilen Sie den Link mit Ihren Freunden! Video-Lektionen

(„Untere Leiste“:(„textstyle“: „static“, „textpositionstatic“: „bottom“, „textautohide“: true, „textpositionmarginstatic“: 0, „textpositiondynamic“: „bottomleft“, „textpositionmarginleft“: 24,“ textpositionmarginright“:24, „textpositionmargintop“:24, „textpositionmarginbottom“:24, „texteffect“: „slide“, „texteffecteasing“: „easeOutCubic“, „texteffectduration“: 600, „texteffectslidedirection“: „left“, „texteffectslidedistance“ :30, „texteffectdelay“:500, „texteffectseparate“:false, „texteffect1“: „slide“, „texteffectslidedirection1“: „rechts“, „texteffectslidedistance1“: 120, „texteffecteasing1“: „easeOutCubic“, „texteffectduration1“: 600 ,"texteffectdelay1":1000,"texteffect2":schieben,texteffectslidedirection2:rechts,texteffectslidedistance2:120,texteffecteasing2:easeOutCubic,texteffectduration2:600,texteffectdelay2:1500, textcss: „display:block; padding:12px; text-align:left;“, „textbgcss“: „display:block; position:absolute; top:0px; left:0px; width:100 %; height:100 % ; Hintergrundfarbe:#333333; Deckkraft:0,6; Filter:alpha(Deckkraft=60);","titlecss":"display:block; Position:relativ; Schriftart:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; Position:relativ; Schriftart: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; Farbe:#fff; „margin-top:8px;“, „buttoncss“: „display:block; Position:relativ; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive": "Schriftgröße:12px;", "descriptioncssresponsive": "display:none !important;", "buttoncssresponsive": „“, „addgooglefonts“:false, „googlefonts“: „“, „textleftrightpercentforstatic“:40))