Alle der Geheimnisse der Excel-Funktion CDF (SVERWEIS) für Daten in einer Tabelle zu suchen und abrufen, sie zu einem anderen

Batyanov Denis über die Rechte des Gastes Autor erklärt in diesem Beitrag darüber, wie Daten in eine Excel-Tabelle zu finden und sie zu einem anderen extrahieren, und entdeckt die Geheimnisse der vertikalen Betrachtungsfunktion.

Bei der Arbeit in übertreffen sehr oft gibt es eine Notwendigkeit, Daten in einer Tabelle zu finden und sie zu einem anderen zu entfernen. Wenn Sie nicht wissen, wie es zu tun, dann las ich den Artikel, werden Sie es nicht nur lernen, sondern auch unter herauszufinden, welche Bedingungen die maximale Leistung des Systems verdrängen werden können. Als besonders hochwirksame Techniken, die in Verbindung mit der Funktion der CDF verwendet werden sollen.

Auch wenn Sie Jahren der Nutzung der CDF-Funktion haben, mit hoher Wahrscheinlichkeit wird dieser Artikel für Sie nützlich sein und werden nicht gleichgültig lassen. Ich zum Beispiel, als IT-Spezialist und dann der Führer in der IT, verwendet VLOOKUP 15 Jahre, aber zu befassen sich mit allen Nuancen passiert gerade jetzt, wo ich auf professioneller Basis bin ich zu unterrichten begann Excel Menschen.

CDF - eine Abkürzung für inVertikal, usw.Inspektion. In ähnlicher Weise SVERWEIS - Vertikale LOOKUP. Der Name der Funktion lässt uns vermuten, dass es die Zeilen der Tabelle (vertikal sucht - Sortieranlage und Befestigungs Achse), und nicht in den Spalten (horizontal - die Spalten das Sortieren und Befestigungs Linie). Es sei darauf hingewiesen, dass die CDF eine Schwester hat - das hässliche Entlein, die nie ein Schwan werden wird - ist eine Funktion des PGR (HLOOKUP). PGR, in Bezug auf die CDF Gegensatz erzeugt eine horizontale Suche, aber die Excel-Konzept (und in der Tat das Konzept Organisation von Daten) bedeutet, dass Ihre Tabellen eine kleine Anzahl von Spalten und viele mehr haben Linien. Deshalb ist die Suche nach Zeichenketten, wir oft mehr als die Spalten benötigen. wenn Sie übertreffen zu oft HLOOKUP verwenden, ist es sehr wahrscheinlich, dass es etwas, das Sie nicht in diesem Leben verstehen.

Syntax

VLOOKUP hat vier Parameter:

PPS = ( ; ; [] ), Dann:

- Sollwert (selten) oder ein Verweis auf eine Zelle, die den gewünschten Wert (die überwiegende Mehrheit der Fälle) enthält;

(!) - ein Hinweis auf eine Reihe von Zellen (zweidimensionale Anordnung), die in der ersten Spalte wird Parameterwerte such ;

- die Spaltennummer in dem Bereich von dem zurückgegeben werden;

- dies ist ein sehr wichtiger Parameter, der die Frage beantwortet, als ob die erste Spalte des Bereichs aufsteigend sortiert ist . Wenn das Array sortiert ist, zeigen wir TRUE (TRUE) oder 1, ansonsten - FALSCH (FALSE) oder 0. Wenn Sie diesen Parameter weglassen, standardmäßig ist es gleich 1 ist.

Ich wette, dass viele von denen, die CDF-Funktion als Peeling wissen, fühlen sich die Beschreibung des vierten Parameters lesen kann unangenehm, weil sie verwendet es auf eine etwas anderen Art und Weise zu sehen: in der Regel eine Rede über die genaue Übereinstimmung mit der Suche (FALSCH oder 0) oder etwa dem gleichen Sichtbereich (TRUE oder 1).

Jetzt ist es notwendig, den nächsten Abschnitt ein paar Mal zu straffen und zu lesen, bis Sie ein Gefühl für die Bedeutung von dem, was hat sich bis zum Ende gesagt. Es ist jedes Wort wichtig. Beispiele werden helfen, zu verstehen.

CDF als die gleiche Formel funktioniert besonders

• Typ der Formel I. Wenn der letzte Parameter wird als 1 weggelassen oder angegeben, ist die CDF erwartet, daß die erste Spalte in aufsteigender Reihenfolge sortiert, so dass die Suche in der Zeile zu stoppen, dass unmittelbar vorausgeht, um die Zeile in dem der Wert größer als der gewünschte. Wenn diese Zeile nicht gefunden wird, gibt es die letzte Zeile des Bereichs.
  
• Typ der Formel II. Wenn die zweite Option als 0 angegeben wird, dann nacheinander die CDF tastet die erste Spalte des Arrays und stoppt sofort suchen, wenn zunächst eine exakte Übereinstimmung mit dem Parameter gefunden , sonst wird ein Fehlercode zurückgegeben # N / A (# N / A).
  

die Formeln des Systems

CDF Typ I

CDF Typ II

Implikationen für die Spezies der Formel I

1. Die Formeln können für die Verteilung des Wertebereiches verwendet werden.
2. Wenn die erste Spalte Duplikate enthält und sortiert richtig, wird es die letzte der Zeilen mit doppelten Werten zurückgegeben werden.
3. Wenn wir für den Wert offensichtlich mehr sehen, als Sie die erste Spalte enthalten können, können Sie einfach die letzte Zeile der Tabelle finden, die sehr wertvoll sein können.
4. Diese Ansicht wird einen Fehler # N / A nur zurück, wenn er einen Wert von weniger als oder gleich findet auf den gewünschten.
5. Verstehen Sie, dass die Formel einen falschen Wert zurückgibt, wenn Ihr Array nicht sortiert ist, ziemlich schwierig.

Implikationen für die Spezies der Formel II

Wenn der Wert in der ersten Spalte der Matrix mehrmals festgestellt wird, wählt der Formel, um die erste Reihe für einen späteren Abruf.

Produktivität VLOOKUP

Sie haben den Höhepunkt des Ortes des Artikels erreicht. Es scheint, na ja, was ist der Unterschied, wenn ich als letzter Parameter null oder eins hinweisen? Grundsätzlich sind alle der Punkt, natürlich Null, da es sehr praktisch ist: kein Grund zur Sorge über die erste Spalte des Arrays sortiert, kann man sehen, fand den Wert oder nicht. Aber wenn Sie eine Liste von mehreren tausend Formeln CDF (SVERWEIS) haben, werden Sie feststellen, dass die CDF-Typ-II läuft langsam. Es wird in der Regel alle anfangen zu denken:

• Ich brauche einen leistungsfähigeren Computer;
• Ich brauche eine schnelle Formel zum Beispiel viele Menschen wissen um den INDEX + MATCH (INDEX + MATCH), das angeblich schneller miserable 5-10%.

Und einige Leute denken, dass, sobald die CDF ich beginnen Typ und jede mögliche Weise liefern die erste Spalte als die Geschwindigkeit des CDF Betrieb sortieren wird 57 mal erhöhen. Ich schreibe die Wörter - In siebenundfünfzig TIMES! In 57% und 5700%. Diese Tatsache Ich habe ziemlich zuverlässig.

Das Geheimnis um so schnelle Arbeit liegt darin, dass Sie mit dem sortierten Array anwenden können, ist äußerst effizienter Suchalgorithmus, der als binäre Suche (Bisektionsmethode bekannt ist, Dichotomie). Also ich Art von CDF verwendet sie, und der CDF-Typ II sucht ohne Optimierung im Allgemeinen. Das gleiche gilt für das MATCH (MATCH), die den gleichen Parameter enthält und auch Funktion PASST (LOOKUP), die nur auf sortierten Arrays und aktiviert in Excel für die Kompatibilität mit Lotus arbeitet 1-2-3.

Nachteile Formel

Nachteile der CDF liegen auf der Hand: Erstens, es soll nur die erste Spalte dieser Matrix, und zweitens, nur rechts von dieser Spalte. Und wie Sie wissen, kann es gut sein, dass die Spalte, die die notwendigen Informationen auf der linken Seite der Spalte enthält, in dem wir suchen. Ohne diesen Mangel bereits eine Reihe von Formeln INDEX + MATCH (INDEX + MATCH) genannt, die es die flexibelste Lösung zu extrahieren Daten aus Tabellen im Vergleich mit dem CDF (VLOOKUP) macht.

Einige Aspekte der Anwendung der Formel im wirklichen Leben

Bereichssuche

Klassische Darstellung des Suchbereiches - das Problem der Größe der Bestellung Rabatte bestimmen.

Suche nach Textstrings

Natürlich versucht die CDF-Nummern nicht nur, sondern auch Text. Es ist daran zu erinnern, dass der Fall empfindliche Formel unterscheidet nicht. Wenn Sie Platzhalter verwenden, ist es möglich, eine Fuzzy-Suche zu organisieren. Es gibt zwei Wildcard „?“ - ersetzt ein beliebiges Zeichen in der Textzeichenfolge „*“ - ersetzt eine beliebige Anzahl von beliebigen Zeichen.

Kampf mit Leerzeichen

Oft stellt sich die Frage, wie das Problem der zusätzlichen Leerzeichen bei der Suche zu lösen. Wenn der Look-Up-Tabelle noch möglich ist, um sie zu reinigen, wird der erste Parameter der Formel CDF nicht immer auf dich verlassen. Wenn daher das Risiko der Zellen zusätzlichen Raumes der Verstopfung vorhanden ist, ist es möglich, die TRIM (TRIM) Funktion zur Reinigung zu verwenden.

Ein anderes Datenformat

Wenn die erste CDF-Parameter-Funktion bezieht sich auf eine Zelle, die eine Zahl enthält, die aber gespeichert in Zelle als Text und die erste Spalte der Matrix enthält die Nummern in dem richtigen Format, wird die Suche sein, erfolglos. Die umgekehrte Situation. Das Problem wird durch Übertragungsparameter 1 in das gewünschte Format leicht gelöst:

PPS = (- D7; Produkte $ A $ 2: $ C $ 5;! 3; 0) - wenn D7 enthält den Text und die Tabelle - Zahlen;

= CDF (D7 & «»); Produkte $ A $ 2: $ C $ 5;! 3; 0) - und vice versa.

By the way, den Text in Nummer übersetzen können mehrere Möglichkeiten sein, wählen Sie:

• Double Negative -D7.
• Multiplikationseinheit D7 * 1.
• Addition mit Null D7 + 0.
• Der Aufbau des ersten Grades D7 ^ 1.

Übersetzung des Textes erzeugt, indem sie mit einer leeren Zeichenkette Kupplung, die Excel den Datentyp konvertieren verursacht.

Wie die Ausgabe # N / A zu unterdrücken

Es ist sehr bequem mit IFERROR Funktion (IFERROR) zu tun.

Zum Beispiel: = IFERROR (CDF (D7; Produkte $ A $ 2: $ C $ 5;! 3; 0); «»).

Wenn die CDF einen Fehlercode # N / A zurückkehren wird, dann wird es IFERROR Ersatz und Parameter 2 (in diesem Fall wird eine leere Zeichenkette) abzufangen, und Wenn kein Fehler aufgetreten ist, wird diese Funktion so tun, dass es überhaupt nicht, sondern nur die CDF, wieder für die normalen zur Folge hat.

Array

Oft übersehen ein Array-Referenz absolute gemacht wird, und, wenn eine Reihe von „schwimmt“ ziehen. Denken Sie daran, dass anstelle von A2: C5 verwendet werden soll, $ A $ 2: 5 $ C $.

Eine gute Idee ist es, ein Referenzarray auf einem gesonderten Blatt in der Arbeitsmappe zu platzieren. Wird es nicht unter den Füßen, und wird gespeichert haben.

Noch mehr gute Idee, auf der Ankündigung des Arrays als benannten Bereich.

Viele Benutzer verwendet werden, wenn die Struktur des Array-Typ A spezifiziert: C, vollständig die Spalten angibt. Dieser Ansatz hat ein Recht zu existieren, da Sie davon ab, um zu verfolgen, dadurch erspart, dass Ihr Array alle Fäden enthält. Wenn Sie eine Linie auf ein Stück des ursprünglichen Arrays hinzufügen, spezifiziert der Bereich als A: C, müssen nicht eingestellt werden. Natürlich führt diese Syntax Excel ein bisschen mehr Arbeit als mit der genauen Angabe des Bereichs zu verbringen, aber der Daten-Overhead ist zu vernachlässigen. Wir sprechen von Hundertstelsekunden.

Nun, am Rande des Genies - zu Ausgabe in Form eines Arrays Smart-Tisch.

Verwendung COLUMN Funktion zur Extraktionskolonne Angabe

Wenn die Tabelle, in denen Sie die Daten extrahieren, die CDF verwendet wird, hat die gleiche Struktur wie die Look-Up-Tabelle, sondern nur umfasst, kann eine minimale Anzahl von Zeilen in der Spalte Domäne WRT () -Funktion wird verwendet, um automatisch die Anzahl der wiederherstellbaren Berechnungs Spalten. In diesem Fall werden alle wird die CDF-Formel gleich sein (für den ersten Parameter eingestellt, die automatisch ändert)! Beachten Sie, dass der erste Parameter ist die absolute der Spalte koordinieren.

Erstellen eines zusammengesetzten Schlüssel von & »|» &

Wenn es ein Bedarf an mehr als eine Spalte zu einer Zeit zu sehen ist, ist es notwendig, einen zusammengesetzten Schlüssel für die Suche zu machen. Wenn der zurückgegebene Wert nicht Text (wie hier im Fall des Feldes „Code“) und numerisch, für die Das kam zu einer bequemen Formel SUMIFS (SUMIFS) und ein integrierten Schlüsselspalt nicht erforderlich wäre überhaupt.

Dies ist mein erster Artikel für Layfhakera. Wenn es Ihnen gefallen hat, dann lade ich Sie zu besuchen meine SeiteUnd gerne Ihre Kommentare über die Geheimnisse der Verwendung von SVERWEIS und dergleichen lesen. Danke. :)