Der Hauptzweck des Büros Excel-Programme– Berechnungen durchführen. Das Dokument (Buch) dieses Programms kann viele Blätter mit langen Tabellen enthalten, die mit Zahlen, Text oder Formeln gefüllt sind. Automatisiert schnelle Suche ermöglicht es Ihnen, die benötigten Zellen darin zu finden.

Einfache Suche

Um nach einem Wert zu suchen Excel Tabelle, müssen Sie die Dropdown-Liste des Tools „Suchen und Ersetzen“ auf der Registerkarte „Startseite“ öffnen und auf den Eintrag „Suchen“ klicken. Den gleichen Effekt erzielen Sie mit der Tastenkombination Strg + F.

Im einfachsten Fall müssen Sie im erscheinenden Fenster „Suchen und Ersetzen“ den gewünschten Wert eingeben und auf „Alle suchen“ klicken.

Wie Sie sehen können, wurden unten im Dialogfeld Suchergebnisse angezeigt. Die gefundenen Werte sind in der Tabelle rot unterstrichen. Wenn Sie statt „Alle suchen“ auf „Weitersuchen“ klicken, wird zuerst die erste Zelle mit diesem Wert durchsucht, und wenn Sie erneut klicken, wird die zweite Zelle durchsucht.

Die Textsuche erfolgt auf die gleiche Weise. In diesem Fall wird der gesuchte Text in die Suchleiste eingegeben.

Sollen Daten oder Texte nicht in der gesamten Excel-Tabelle durchsucht werden, muss zunächst der Suchbereich ausgewählt werden.

Erweiterte Suche

Angenommen, Sie möchten alle Werte im Bereich von 3000 bis 3999 finden. In diesem Fall würden Sie 3??? in die Suchleiste eingeben. Platzhalter „?“ ersetzt alle anderen.

Bei der Analyse der Suchergebnisse kann festgestellt werden, dass das Programm neben den richtigen 9 Ergebnissen auch unerwartete Ergebnisse lieferte, die rot hervorgehoben sind. Sie sind mit dem Vorhandensein der Zahl 3 in einer Zelle oder Formel verbunden.

Sie können mit den meisten Ergebnissen zufrieden sein und die falschen ignorieren. Doch die Suchfunktion in Excel 2010 kann deutlich genauer arbeiten. Zu diesem Zweck dient das Optionen-Tool im Dialogfeld.

Durch Klicken auf „Optionen“ hat der Benutzer die Möglichkeit, erweiterte Suchen durchzuführen. Achten wir zunächst auf den Punkt „Suchbereich“, der standardmäßig auf „Formeln“ eingestellt ist.

Dies bedeutet, dass die Suche auch in den Zellen durchgeführt wurde, in denen kein Wert, sondern eine Formel steht. Das Vorhandensein der Zahl 3 in ihnen führte zu drei falschen Ergebnissen. Wenn Sie „Werte“ als Suchbereich auswählen, suchen Sie nur nach Daten und die mit Formelzellen verknüpften falschen Ergebnisse verschwinden.

Um das einzig verbleibende falsche Ergebnis in der ersten Zeile zu entfernen, müssen Sie im erweiterten Suchfenster den Eintrag „Gesamte Zelle“ auswählen. Danach ist das Suchergebnis zu 100 % korrekt.

Dieses Ergebnis könnte erreicht werden, indem sofort das Element „Gesamte Zelle“ ausgewählt wird (sogar der Wert „Formel“ im „Suchbereich“ belassen wird).

Kommen wir nun zum Punkt „Suchen“.

Wenn Sie anstelle der Standardeinstellung „Auf Blatt“ „In Arbeitsmappe“ auswählen, müssen Sie sich nicht auf dem Blatt mit den gesuchten Zellen befinden. Der Screenshot zeigt, dass der Benutzer die Suche gestartet hat, während er sich auf leerem Blatt 2 befand.

Der nächste Eintrag im erweiterten Suchfenster ist „Ansicht“, was zwei Bedeutungen hat. Die Standardeinstellung ist „zeilenweise“, was bedeutet, dass die Zellen Zeile für Zeile gescannt werden. Die Auswahl eines anderen Werts – „nach Spalten“ – ändert lediglich die Suchrichtung und die Reihenfolge der Ergebnisse.

Bei der Suche in Dokumenten Microsoft Excel, Sie können auch einen anderen Platzhalter verwenden – „*“. Wenn das betrachtete „?“ bedeutete ein beliebiges Zeichen, dann ersetzt „*“ nicht eins, sondern beliebig viele Zeichen. Unten sehen Sie einen Screenshot einer Suche nach Louisiana.

Manchmal ist es notwendig, bei der Suche die Groß-/Kleinschreibung von Zeichen zu berücksichtigen. Wenn das Wort Louisiana großgeschrieben wird, ändern sich die Suchergebnisse nicht. Wenn Sie jedoch im erweiterten Suchfenster „Groß-/Kleinschreibung berücksichtigen“ auswählen, schlägt die Suche fehl. Das Programm betrachtet die Wörter „Louisiana“ und „Louisiana“ als unterschiedlich und findet das erste davon natürlich nicht.

Arten der Suche

Nach Übereinstimmungen suchen

Manchmal ist es notwendig, doppelte Werte in einer Tabelle zu erkennen. Um nach Übereinstimmungen zu suchen, müssen Sie zunächst einen Suchbereich auswählen. Öffnen Sie dann auf derselben Registerkarte „Startseite“ in der Gruppe „Stile“ die Option „ Bedingte Formatierung" Wählen Sie anschließend nacheinander die Punkte „Regeln zum Hervorheben von Zellen“ und „Werte wiederholen“ aus.

Das Ergebnis ist im folgenden Screenshot dargestellt.

Bei Bedarf kann der Benutzer die Farbe der visuellen Anzeige übereinstimmender Zellen ändern.

Filtration

Eine andere Art der Suche ist das Filtern. Nehmen wir an, dass der Benutzer in Spalte B numerische Werte im Bereich von 3000 bis 4000 finden möchte.

Wie Sie sehen, werden nur Zeilen angezeigt, die die eingegebene Bedingung erfüllen. Der Rest wurde vorübergehend versteckt. Um zum Ausgangszustand zurückzukehren, wiederholen Sie Schritt 2.

Am Beispiel von Excel 2010 wurden verschiedene Suchmöglichkeiten besprochen. Wie suche ich in Excel anderer Versionen? Beim Übergang zur Filterung gibt es in Version 2003 einen Unterschied. Im Menü „Daten“ sollten Sie nacheinander die Befehle „Filter“, „Auto-Filter“, „Bedingung“ und „Benutzerdefinierter Auto-Filter“ auswählen.

Video: Suche in einer Excel-Tabelle

Es ist auf jeden Fall einfacher, eine, wenn auch große, gesamte Tabelle oder benachbarte Zellbereiche zu durchsuchen, als mehrere Tabellen, die in Teile unterteilt sind, die über verschiedene, nicht benachbarte Bereiche oder sogar in separaten Blättern verteilt sind. Auch wenn du es tust automatische Suche gleichzeitig über mehrere Tische hinweg kann es zu erheblichen Hürden kommen. Es ist jedoch schwierig, manchmal fast unmöglich, alle Daten in einer Tabelle zu organisieren. An konkretes Beispiel Wir demonstrieren die richtige Lösung für die gleichzeitige Suche über mehrere Tabellen in Excel.

Gleichzeitige Suche über mehrere Bereiche hinweg

Als visuelles Beispiel erstellen wir drei einfache separate Tabellen, die sich in nicht benachbarten Bereichen eines Blattes befinden:

Sie sollten nach der Menge suchen, die zur Herstellung von 20 Produktstücken erforderlich ist. Leider befinden sich diese Daten in verschiedenen Spalten und Zeilen. Daher müssen Sie zunächst prüfen, wie lange die Herstellung dieser Produkte dauern wird (erste Tabelle).

Basierend auf den erhaltenen Daten müssen Sie sofort mit der Suche in einer anderen Tabelle fortfahren und herausfinden, wie viele Arbeiter an einem bestimmten Produktionsvolumen beteiligt sein sollten. Das erhaltene Ergebnis sollte mit den Daten in der dritten Tabelle verglichen werden. So ermitteln wir in einem Suchvorgang über drei Tabellen hinweg sofort die notwendigen Kosten (Höhe).

Der durchschnittliche Excel-Benutzer würde nach einer Lösung suchen, indem er formelbasierte Funktionen wie SVERWEIS verwendet. Und es würde eine Suche in drei Schritten durchführen (separat für jede Tabelle). Es stellt sich heraus, dass Sie sofort ein fertiges Ergebnis erhalten können, indem Sie eine Suche in nur einem Schritt mit einer speziellen Formel durchführen. Dafür:

1. Geben Sie in Zelle E6 den Wert 20 ein, der die Bedingung für die Suchanfrage darstellt.
2. Geben Sie in Zelle E7 die folgende Formel ein:

Produktionskosten für 20 Stück. ein bestimmtes Produkt.

So funktioniert die Formel mit VLOOKUP in mehreren Tabellen:

Das Funktionsprinzip dieser Formel basiert auf der sequentiellen Suche nach allen Argumenten für die Haupt-SVERWEIS-Funktion (die erste). Zunächst durchsucht die dritte SVERWEIS-Funktion die erste Tabelle nach der Zeit, die zur Herstellung von 20 Stück des als Wert für Zelle E6 angegebenen Produkts erforderlich ist (der dann bei Bedarf geändert werden kann). Dann sucht die zweite SVERWEIS-Funktion nach dem Wert für das erste Argument der Hauptfunktion.

Als Ergebnis der Suche nach der dritten Funktion erhalten wir den Wert 125, der das erste Argument für die zweite Funktion ist. Nachdem alle Parameter empfangen wurden, sucht die zweite Funktion in der zweiten Tabelle nach der Anzahl der für die Produktion erforderlichen Arbeitskräfte. Als Ergebnis wird der Wert 5 zurückgegeben, der dann von der Hauptfunktion verwendet wird. Basierend auf allen empfangenen Daten gibt die Formel das Endergebnis der Berechnung zurück. Nämlich der Betrag von 1.750 US-Dollar, der für die Herstellung von 20 Stück eines bestimmten Produkts erforderlich ist.

Nach diesem Prinzip können Sie Formeln für die VLOOKUP-Funktion aus mehreren Blättern verwenden.

Guten Tag, liebe Habro-Bewohner!

Von Zeit zu Zeit stehen einige (vielleicht mehr als einige) von uns vor der Aufgabe, kleine Datenmengen zu verarbeiten, angefangen bei der Zusammenstellung bis hin zur Analyse Haushaltsbudget und endet mit allen Berechnungen für Arbeit, Studium usw. Das dafür vielleicht am besten geeignete Tool ist Microsoft Excel (oder vielleicht andere Analoga, die jedoch weniger verbreitet sind).

Die Suche ergab nur einen Artikel über Habré zu einem ähnlichen Thema – „Talmud mit Formeln in Google SpreadSheet“. Es gibt eine gute Beschreibung der grundlegenden Dinge für die Arbeit mit Excel (obwohl es nicht zu 100 % auf Excel selbst eingeht).

Nachdem wir einen bestimmten Pool an Anfragen/Aufgaben gesammelt hatten, entstand die Idee, diese zu tippen und Vorschläge zu machen mögliche Lösungen(wenn auch nicht alle möglich, aber schnell Ergebnisse liefernd).

Wir werden über die Lösung der häufigsten Probleme sprechen, auf die Benutzer stoßen.

Die Beschreibung der Lösungen ist wie folgt aufgebaut: Es wird ein Fall vorgelegt, der die Ausgangsaufgabe enthält, die nach und nach komplizierter wird, und zu jedem Schritt wird eine detaillierte Lösung mit Erläuterungen angegeben. Die Namen der Funktionen werden auf Russisch angegeben, bei der ersten Erwähnung wird jedoch der ursprüngliche Name auf Russisch in Klammern angegeben. Englische Sprache(da erfahrungsgemäß die überwiegende Mehrheit der Nutzer die russische Version installiert hat).

Fall 1: Logikfunktionen und passende Funktionen
„Ich habe eine Reihe von Werten in einer Tabelle und es ist notwendig, dass bei Erfüllung einer bestimmten Bedingung/eines bestimmten Satzes von Bedingungen ein bestimmter Wert angezeigt wird“ (c) Benutzer

Die Daten werden üblicherweise in tabellarischer Form dargestellt:

Zustand:

• wenn der Wert in der Spalte „Menge“ größer als 5 ist,
• Dann müssen Sie in der Spalte „Ergebnis“ den Wert „Keine Bestellung erforderlich“ anzeigen.

Die Formelsyntax lautet wie folgt:
IF(logical_expression, [value_if_true], [value_if_false])

• Ein logischer Ausdruck ist ein Ausdruck, der als TRUE oder FALSE ausgewertet wird.
• Value_if_true – der Wert, der gedruckt wird, wenn der logische Ausdruck wahr ist
• Value_if_false – der Wert, der gedruckt wird, wenn der logische Ausdruck falsch ist

=IF(C5>5, „Keine Bestellung erforderlich“, „Bestellung erforderlich“)

Es kommt vor, dass die Bedingung komplexer ist, zum Beispiel die Erfüllung von 2 oder mehr Bedingungen:

• wenn der Wert in der Spalte „Menge“ größer als 5 ist und der Wert in der Spalte „Typ“ „A“ ist

• Boolean_value1-2 usw. - eine zu prüfende Bedingung, deren Auswertung den Wert TRUE oder FALSE ergibt

=WENN(UND(C2>5,B2=„A“),1,0)

Versuchen wir, die Aufgabe zu erschweren – eine neue Bedingung:

• wenn der Wert in der Spalte „Menge“ 10 und der Wert in der Spalte „Typ“ „A“ ist
• oder der Wert in der Spalte „Menge“ ist größer oder gleich 5 und der Typwert ist B
• Dann müssen Sie in der Spalte „Ergebnis“ den Wert „1“ anzeigen, andernfalls „0“.

=IF(OR(AND(C2=10,B2=„A“); AND(C2>=5,B2=„B“)),1,0)

Kommen wir nun zur nächsten Situation:
Stellen wir uns vor, dass abhängig vom Wert in der Spalte „Bedingung“ eine bestimmte Bedingung in der Spalte „Ergebnis“ angezeigt werden soll; unten ist die Entsprechung zwischen den Werten und dem Ergebnis.
Zustand:

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=WENN(A2=1,„A“, WENN(A2=2,„B“, WENN(A2=3,„C“, WENN(A2=4,„D“,0))))

Wie Sie sehen, ist das Schreiben einer solchen Formel nicht nur wenig komfortabel und umständlich, sondern es kann auch für einen unerfahrenen Benutzer einige Zeit in Anspruch nehmen, sie im Fehlerfall zu bearbeiten.
Der Nachteil dieses Ansatzes besteht darin, dass er für eine kleine Anzahl von Bedingungen anwendbar ist, da alle manuell eingegeben und unsere Formel auf große Größen „aufgeblasen“ werden müssen; der Ansatz zeichnet sich jedoch durch eine vollständige „Allesfresser“-Werte aus ​​und vielseitige Einsatzmöglichkeiten.

Alternativlösung_1:
Verwenden der CHOOSE-Formel
Funktionssyntax:
SELECT(index_number, value1, [value2], ...)

• Indexnummer – die Nummer des ausgewählten Wertarguments. Die Indexnummer muss eine Zahl zwischen 1 und 254, eine Formel oder ein Verweis auf eine Zelle sein, die eine Zahl zwischen 1 und 254 enthält.
• Wert1, Wert2,... – ein Wert von 1 bis 254 Wertargumenten, aus dem die Funktion „SELECT“ anhand der Indexnummer den auszuführenden Wert oder die auszuführende Aktion auswählt. Argumente können Zahlen, Zellbezüge, bestimmte Namen, Formeln, Funktionen oder Text sein.

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=WAHL(A2, „A“, „B“, „C“, „D“)

• 1 = A
• 2 = B
• 3 = B
• 5 = G

=WAHL(A31, „A“, „B“, „C“, „D“)

Alternativlösung_2:
Hier kommen wir zu einem der beliebtesten Excel-Funktionen, dessen Beherrschung jeden Büroangestellten automatisch zu einem „erfahrenen Excel-Benutzer“ macht /Sarkasmus/.
Formelsyntax:
VLOOKUP(lookup_value, table, columns_number, [interval_lookup])

• Suchwert – der Wert, nach dem die Funktion sucht.
• Eine Tabelle ist ein Bereich von Zellen, die Daten enthalten. In diesen Zellen findet die Suche statt. Werte können Text, numerisch oder boolesch sein.
• Spaltennummer – die Nummer der Spalte im Argument „Tabelle“, aus der der Wert abgeleitet wird, wenn eine Übereinstimmung vorliegt. Es ist wichtig zu verstehen, dass die Spalten nicht entlang des allgemeinen Blattrasters (A.B,C,D usw.) gezählt werden, sondern innerhalb des im Argument „Tabelle“ angegebenen Arrays.
• Interval_lookup – bestimmt, ob die Funktion eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung finden soll.

Anschließend wird im Arbeitsteil der Tabelle eine Formel mit einem Link zum zuvor ausgefüllten Nachschlagewerk notiert. Diese. Im Verzeichnis wird in Spalte „D“ der Wert aus Spalte „A“ gesucht und bei einer Übereinstimmung wird der Wert aus Spalte „E“ in Spalte „B“ angezeigt.
Formelsyntax:
Ausgabe des Ergebnisses in Zelle B2:

Stellen Sie sich nun eine Situation vor, in der Sie Daten aus einer anderen Tabelle in eine Tabelle ziehen müssen, die Tabellen jedoch nicht identisch sind. Siehe Beispiel unten

Es ist ersichtlich, dass die Zeilen in den Spalten „Produkt“ beider Tabellen nicht übereinstimmen, dies stellt jedoch kein Hindernis für die Verwendung der Funktion „SVERWEIS“ dar.
Ausgabe des Ergebnisses in Zelle B2:

• Eine Referenz ist eine Zelle oder ein Zellbereich, für die Sie die Spaltennummer zurückgeben möchten.

=VLOOKUP($A3,$H$3:$M$6, COLUMN(),0)

Wir entwickeln das Thema weiter und verkomplizieren die Bedingung: Stellen Sie sich vor, wir haben zwei Verzeichnisse mit unterschiedlichen Daten zu Produkten und wir müssen die Werte in der Tabelle anzeigen, wobei das Ergebnis davon abhängt, welche Art von Verzeichnis im „Verzeichnis“ angegeben ist. Spalte
Zustand:

• Wenn in der Spalte „Verzeichnis“ die Zahl 1 angegeben ist, sollten die Daten aus der Tabelle „Verzeichnis_1“ entnommen werden, wenn die Zahl 2 ist, dann aus der Tabelle „Verzeichnis_2“ entsprechend dem angegebenen Monat

Die Lösung, die mir sofort in den Sinn kommt, ist folgende:

=IF($B3=1; VLOOKUP($A3,$G$3:$I$6; COLUMN()-1,0); VLOOKUP($A3,$K$3:$M$6; COLUMN()-1;0 ))

=VLOOKUP($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

Bonus: VLOOKUP basierend auf zwei oder mehr Merkmalen im Argument „search_value“.
Zustand:

• Stellen wir uns vor, wir haben wie immer ein Array von Daten in Tabellenform (wenn nicht, dann bringen wir die Daten dorthin), wir müssen Werte aus dem Array basierend auf bestimmten Merkmalen abrufen und sie in einer anderen Tabellenform platzieren .

Wie aus ersichtlich ist tabellarische Formulare Jeder Artikel hat nicht nur einen Namen (der nicht eindeutig ist), sondern gehört auch zu einer bestimmten Klasse und verfügt über eine eigene Verpackungsoption.
Durch eine Kombination aus Name, Klasse und Verpackung können wir ein neues Merkmal erstellen; dazu erstellen wir in der Tabelle mit den Daten eine zusätzliche Spalte „Zusatzmerkmal“, die wir nach folgender Formel füllen:

=CONCATENATE(H3;"_";I3;"_";J3)

=IFERROR(SVERWEIS(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

Diese Technik kann für eine größere Anzahl von Merkmalen verwendet werden, die einzige Bedingung ist die Eindeutigkeit der resultierenden Kombinationen; wenn diese nicht erfüllt ist, wird das Ergebnis falsch sein.

Case_3 Suche nach einem Wert in einem Array oder wenn SVERWEIS uns nicht weiterhelfen kann

Betrachten wir eine Situation, in der wir verstehen müssen, ob das Zellenarray die von uns benötigten Werte enthält.
Aufgabe:

• Die Spalte „Suchbedingung“ enthält einen Wert und Sie müssen feststellen, ob er in der Spalte „Sucharray“ vorhanden ist

Wie wir sehen, ist die Funktion „SVERWEIS“ hier machtlos, denn Wir suchen nicht nach einer exakten Übereinstimmung, sondern nach dem Vorhandensein des benötigten Werts in der Zelle.
Um das Problem zu lösen, ist eine Kombination mehrerer Funktionen erforderlich, nämlich:
"WENN"
„WENN FEHLER“
"UNTERE"
"FINDEN"

Der Einfachheit halber: Wir haben „IF“ bereits weiter oben besprochen, also kommen wir zur Funktion „IFERROR“.

IFERROR(Wert, Fehlerwert)

• Der Wert ist das Argument, das auf Fehler überprüft wird.
• Value_on_error – der zurückgegebene Wert, wenn bei der Berechnung der Formel ein Fehler auftritt. Möglich folgende Typen Fehler: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? und #LEER!.

"UNTERE"

• Text – in Kleinbuchstaben umgewandelter Text.

Schauen wir uns nun die Syntax der FIND-Funktion genauer an.

FINDEN(Suchtext, angezeigter_Text, [Startposition])

• Search_text – der Text, der gefunden werden muss.
• Suchtext – der Text, in dem Sie den gesuchten Text finden möchten.
• Start_position – das Zeichen, von dem aus die Suche gestartet werden soll. Das erste Zeichen im Text „view_text“ hat die Nummer 1. Wenn die Nummer nicht angegeben wird, wird standardmäßig 1 verwendet.

=IF(IFERROR(FIND(LINE(A2), LINE(E2),1),0)=0,“fail“,“bingo!“)

1. LOWER(A2) – wandelt das Search_Text-Argument in Zelle A2 in Kleinbuchstaben um
2. Die Funktion FIND beginnt mit der Suche nach dem konvertierten Argument Search_Text im Array Search_Text, das von der Funktion LOWER(E2) ebenfalls in Kleinbuchstaben umgewandelt wird.
3. Wenn die Funktion eine Übereinstimmung findet, d. h. Gibt die Seriennummer des ersten Zeichens des passenden Worts/Werts zurück, die TRUE-Bedingung in der „IF“-Formel wird ausgelöst, weil Der resultierende Wert ist nicht Null. Als Ergebnis wird in der Spalte „Ergebnis“ der Wert „Bingo!“ angezeigt.
4. Wenn die Funktion jedoch keine Übereinstimmung findet, d. h. Die Seriennummer des ersten Zeichens des passenden Worts/Werts wird nicht angezeigt und anstelle des Werts wird ein Fehler zurückgegeben. Die in der Formel „IFERROR“ enthaltene Bedingung wird ausgelöst und ein Wert gleich „0“ zurückgegeben, was entspricht zur FALSE-Bedingung in der „IF“-Formel, weil der resultierende Wert ist „0“. Als Ergebnis wird in der Spalte „Ergebnis“ der Wert „fehlgeschlagen“ angezeigt.

Wie aus der obigen Abbildung ersichtlich ist, finden wir dank der Funktionen „LOW“ und „FIND“ die gewünschten Werte unabhängig von der Groß-/Kleinschreibung der Zeichen und der Position in der Zelle, müssen jedoch auf Zeile 5 achten.
Der Suchbegriff ist auf „111“ gesetzt, aber das Sucharray enthält den Wert „1111111 Cookies“, aber die Formel gibt das Ergebnis „Bingo!“ zurück. Dies geschieht, weil der Wert „111“ in der Wertereihe „1111111“ enthalten ist und somit eine Übereinstimmung gefunden wird. Ansonsten dieser Zustand wird nicht funktionieren.

Fall_4 Die Suche nach einem Wert in einem Array basierend auf mehreren Bedingungen oder wenn SVERWEIS uns noch mehr nicht weiterhelfen kann

Stellen wir uns eine Situation vor, in der Sie einen Wert aus der „Tabelle mit dem Ergebnis“ in finden müssen zweidimensionales Array„Verzeichnis“ für mehrere Bedingungen, nämlich den Wert „Name“ und „Monat“.
Die tabellarische Form der Aufgabe sieht folgendermaßen aus:

Zustand:

• Es ist notwendig, die Daten entsprechend der Übereinstimmung der Bedingungen „Name“ und „Monat“ in die Tabelle mit dem Ergebnis einzutragen.

Syntax der INDEX-Funktion

INDEX(Array, Zeilennummer, [Spaltennummer])

• Array – ein Bereich von Zellen, aus denen Werte angezeigt werden, wenn ihre Suchbedingungen übereinstimmen.
• Wenn das Array nur eine Zeile oder eine Spalte enthält, ist das Argument row_number bzw. columns_number optional.
• Wenn das Array mehr als eine Zeile und eine Spalte einnimmt und nur eines der Argumente row_number und columns_number angegeben ist, gibt die INDEX-Funktion ein Array zurück, das aus der gesamten Zeile oder der gesamten Spalte des Array-Arguments besteht.
• Zeilennummer – die Nummer der Zeile im Array, aus der Sie einen Wert zurückgeben möchten.
• Spaltennummer – die Nummer der Spalte im Array, aus der Sie einen Wert zurückgeben möchten.

Syntax der MATCH-Funktion

MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value ist der Wert, der mit den Werten im Argument lookup_array übereinstimmt. Das Argument „lookup_value“ kann ein Wert (Zahl, Text oder Boolescher Wert) oder ein Verweis auf eine Zelle sein, die einen solchen Wert enthält.
• Looked_array – der Zellbereich, in dem die Suche durchgeführt wird.
• match_type ist ein optionales Argument. Die Zahl ist -1, 0 oder 1.

Teil der Formel:

MATCH($A4,$I$4:$I$7,0)

MATCH(B$3,$J$3:$L$3,0)

Somit haben wir die Koordinaten des Punktes (1; #N/A) erhalten, die die Funktion „INDEX“ verwendet, um im Argument „Array“ zu suchen.
Die vollständig geschriebene Funktion für Zelle B4 würde so aussehen:

=INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0))

=INDEX($J$4:$L$7,1,#N/A))

Um diesen negativen Effekt zu neutralisieren, verwenden wir die Funktion „IFERROR“, über die wir zuvor gelesen haben, und ersetzen den im Fehlerfall zurückgegebenen Wert durch „0“, dann sieht die Formel wie folgt aus:

=IFERROR(INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0)),0)

Wie Sie im Bild sehen können, beeinträchtigen die „#N/A“-Werte unsere nachfolgenden Berechnungen anhand der Werte in der Ergebnistabelle nicht mehr.

Fall_5 Einen Wert in einem Zahlenbereich finden

Stellen wir uns vor, dass wir Zahlen in einem bestimmten Bereich ein bestimmtes Vorzeichen geben müssen.
Zustand:
Abhängig vom Preis des Produkts sollte es einer bestimmten Kategorie zugeordnet werden
Wenn der Wert im Bereich liegt

• Von 0 bis 1000 = A
• Von 1001 bis 1500 = B
• Von 1501 bis 2000 = B
• Von 2001 bis 2500 = G
• Mehr als 2501 = D

Die LOOKUP-Funktion gibt einen Wert aus einer Zeile, Spalte oder einem Array zurück. Die Funktion hat zwei syntaktische Formen: Vektor- und Array-Form.

LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value ist der Wert, nach dem die LOOKUP-Funktion im ersten Vektor sucht. Lookup_value kann eine Zahl, ein Text, ein boolescher Wert, ein Name oder eine Wertreferenz sein.
• Watch_vector ist ein Bereich, der aus einer Zeile oder einer Spalte besteht. Die Werte im Argument „lookup_vector“ können Text, Zahlen oder boolesche Werte sein.
• Die Werte im view_vector-Argument müssen in aufsteigender Reihenfolge sein: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; Andernfalls gibt die LOOKUP-Funktion möglicherweise ein falsches Ergebnis zurück. Klein- und Großbuchstaben gelten als gleichwertig.
• result_vector ist ein Bereich, der aus einer Zeile oder Spalte besteht. Der result_vector muss die gleiche Größe wie der lookup_vector haben.

=VIEW(E3,$A$3:$A$7,$B$3:$B$7)

Die Argumente „View_vector“ und „Result_vector“ können in Array-Form geschrieben werden – in diesem Fall müssen Sie sie nicht in einer separaten Tabelle auf einer Excel-Tabelle anzeigen.
In diesem Fall sieht die Funktion folgendermaßen aus:
Ausgabe des Ergebnisses in Zelle B3:
=VIEW(E3;(0;1001;1501;2001;2501);("A",B",C",D",D",D"))

Fall_6 Summierung von Zahlen nach Merkmalen

Um Zahlen basierend auf bestimmten Merkmalen zu summieren, können Sie drei verschiedene Funktionen verwenden:
SUMIF – summiert nach nur einem Attribut
SUMIFS – Summen über mehrere Merkmale
SUMMENPRODUKT – Summen über mehrere Merkmale
Es gibt auch eine Option mit „SUM“ und der Array-Formelfunktion, wenn die Formel „SUM“ zu einem Array erhoben wird:
((=SUMME(()*()))
Dieser Ansatz ist jedoch recht umständlich und wird in seiner Funktionalität vollständig durch die Formel „SUMMENPRODUKT“ abgedeckt
Nun zu weiteren Details zur „SUMPRODUCT“-Syntax:

SUMMENPRODUKT(array1, [array2], [array3],...)

• Array1 ist das erste Array, dessen Komponenten multipliziert und anschließend die Ergebnisse addiert werden müssen.
• Array2, Array3… – von 2 bis 255 Arrays, deren Komponenten multipliziert und dann die Ergebnisse addiert werden müssen.

• Finden Sie die Gesamtversandkosten für jedes Produkt für einen bestimmten Zeitraum:

Wie aus der Tabelle mit den Daten hervorgeht, ist es zur Berechnung der Kosten erforderlich, den Preis mit der Menge zu multiplizieren und den resultierenden Wert unter Anwendung der Auswahlbedingungen in die Tabelle mit dem Ergebnis zu übertragen.
Die SUMPROIZ-Formel ermöglicht jedoch die Durchführung solcher Berechnungen innerhalb der Formel.
Ausgabe des Ergebnisses in Zelle B4:

=SUMMENPRODUKT(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Schauen wir uns die Formel in Teilen an:
– Setzen Sie die Auswahlbedingung in der Spalte „Name“ der Tabelle mit Daten zur Spalte „Name“ in der Tabelle mit dem Ergebnis

($K$3:$K$11>=B$3)*($K$3:$K$11 – Wir legen eine Bedingung fest, die auf dem Zeitrahmen basiert. Das Datum ist größer oder gleich dem ersten Tag des aktuellen Monats, aber kleiner als der erste Tag des nächsten Monats. Ebenso befindet sich eine Bedingung in einer Tabelle mit dem Ergebnis, ein Array in einer Tabelle mit Daten.
– Multiplizieren Sie die Spalten „Menge“ und „Preis“ in der Tabelle mit den Daten.
Der unbestrittene Vorteil dieser Funktion ist die freie Reihenfolge der Aufnahmebedingungen; sie können in beliebiger Reihenfolge geschrieben werden, dies hat keinen Einfluss auf das Ergebnis.
Ergebnis:

Jetzt verkomplizieren wir die Bedingung und fügen die Anforderung hinzu, dass die Auswahl für den Namen „Kekse“ nur in den Klassen „klein“ und „groß“ erfolgt und für den Namen „Rollen“ alles außer der Klasse „mit Marmelade“:

Ausgabe des Ergebnisses in Zelle B4:

=SUMMENPRODUKT(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
Der Formel zur Auswahl von Cookies wurde eine neue Bedingung hinzugefügt:

(($I$3:$I$11=„klein“)+($I$3:$I$11=„groß“))

– Wie Sie sehen, werden zwei oder mehr Bedingungen in einer Spalte durch das „+“-Symbol und das Einschließen der Bedingungen in zusätzliche Klammern in eine separate Gruppe unterteilt.
Der Formel für die Auswahl nach Rollen wurde außerdem eine neue Bedingung hinzugefügt:

=SUMPRODUCT(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 „mit Marmelade“);($L$3:$L$11)*($K$3:$K$11))

Das:

($I$3:$I$11<>„mit Marmelade“)

– Tatsächlich war es in dieser Formel möglich, eine Auswahlbedingung auf die gleiche Weise wie bei der Auswahl durch Cookies zu schreiben, aber dann müssten Sie drei Bedingungen in der Formel auflisten, in diesem Fall ist es einfacher, eine Ausnahme zu schreiben - ist nicht gleich „mit Marmelade“, hierfür verwenden wir den Wert „<>».
Wenn Gruppen von Features/Klassen im Voraus bekannt sind, ist es im Allgemeinen besser, sie zu diesen Gruppen zusammenzufassen und Nachschlagewerke zu erstellen, als alle Bedingungen in eine Funktion zu schreiben und sie dadurch aufzublähen.
Ergebnis:

Nun, hier sind wir am Ende unseres kurzen Handbuchs angelangt, das tatsächlich viel länger sein könnte, aber das Ziel bestand immer noch darin, eine Lösung für die häufigsten Situationen bereitzustellen, und nicht darin, die Lösung für bestimmte (aber viel interessantere) Fälle zu beschreiben ).
Ich hoffe, dass das Handbuch jemandem bei der Lösung von Problemen mit Excel hilft, denn das bedeutet, dass meine Arbeit nicht umsonst war!

Vielen Dank für Ihre Zeit!

IN Microsoft-Dokumente Excel, das aus einer großen Anzahl von Feldern besteht, muss häufig bestimmte Daten, Zeilennamen usw. finden. Es ist sehr umständlich, wenn Sie eine große Anzahl von Zeilen durchsehen müssen, um das richtige Wort oder den richtigen Ausdruck zu finden. Die integrierte Microsoft Excel-Suche hilft Ihnen, Zeit und Nerven zu sparen. Lassen Sie uns herausfinden, wie es funktioniert und wie man es verwendet.

Die Suchfunktion in Microsoft Excel bietet die Möglichkeit, über das Fenster „Suchen und Ersetzen“ den gewünschten Text oder numerische Werte zu finden. Darüber hinaus bietet die Anwendung erweiterte Datensuchfunktionen.

Methode 1: Einfache Suche

Eine einfache Datensuche in Excel ermöglicht es Ihnen, alle Zellen zu finden, die den im Suchfenster eingegebenen Zeichensatz (Buchstaben, Zahlen, Wörter usw.) enthalten, ohne Berücksichtigung der Groß- und Kleinschreibung.

Methode 2: Suche nach angegebenem Zellbereich

Wenn Sie eine ziemlich große Tabelle haben, ist es in diesem Fall nicht immer praktisch, das gesamte Blatt zu durchsuchen, da die Suchergebnisse eine große Anzahl von Ergebnissen enthalten können, die im Einzelfall nicht benötigt werden. Es gibt eine Möglichkeit, den Suchraum auf nur einen bestimmten Zellbereich zu beschränken.

Methode 3: Erweiterte Suche

Wie oben erwähnt, umfassen die Suchergebnisse bei einer normalen Suche absolut alle Zellen, die einen sequentiellen Satz von Suchzeichen in beliebiger Form enthalten, unabhängig von der Groß-/Kleinschreibung.

Darüber hinaus kann nicht nur der Inhalt einer bestimmten Zelle, sondern auch die Adresse des Elements, auf das sie verweist, in die Ergebnisse einbezogen werden. Beispielsweise enthält Zelle E2 eine Formel, die die Summe der Zellen A4 und C3 darstellt. Diese Summe ist 10, und diese Zahl erscheint in Zelle E2. Wenn wir jedoch die Zahl „4“ in die Suche eingeben, wird dieselbe Zelle E2 unter den Ergebnissen sein. Wie konnte das passieren? Es ist nur so, dass Zelle E2 als Formel die Adresse für Zelle A4 enthält, in der gerade die gewünschte Zahl 4 enthalten ist.

Aber wie können wir diese und andere offensichtlich inakzeptable Suchergebnisse ausschneiden? Zu diesem Zweck gibt es die erweiterte Excel-Suche.

1. Nach dem Öffnen des Fensters "Suchen und Ersetzen" Klicken Sie mit einer der oben beschriebenen Methoden auf die Schaltfläche "Optionen".



2. Im Fenster erscheinen eine Reihe zusätzlicher Tools zur Verwaltung der Suche. Standardmäßig befinden sich alle diese Tools im gleichen Zustand wie eine normale Suche, bei Bedarf können jedoch Anpassungen vorgenommen werden.

   

   Standardmäßig Funktionen „Streichholzkoffer“ Und „Ganze Zellen“ sind deaktiviert, aber wenn wir die Kontrollkästchen neben den entsprechenden Elementen aktivieren, werden in diesem Fall bei der Ergebnisgenerierung der eingegebene Fall und die genaue Übereinstimmung berücksichtigt. Wenn Sie ein Wort mit einem Kleinbuchstaben eingeben, werden Zellen, die die Schreibweise dieses Worts mit einem Großbuchstaben enthalten, nicht mehr in den Suchergebnissen angezeigt, wie dies standardmäßig der Fall wäre. Darüber hinaus, wenn die Funktion aktiviert ist „Ganze Zellen“, dann werden den Ergebnissen nur Elemente hinzugefügt, die den genauen Namen enthalten. Wenn Sie beispielsweise die Suchabfrage „Nikolaev“ festlegen, werden Zellen mit dem Text „Nikolaev A.D.“ nicht zu den Suchergebnissen hinzugefügt.

   

   Standardmäßig wird die Suche nur auf der aktiven Excel-Tabelle durchgeführt. Aber wenn der Parameter "Suchen" Sie werden zur Position wechseln "Im Buch", dann wird die Suche über alle Blätter der geöffneten Datei durchgeführt.

   

   Im Parameter "Durchsuche" Sie können die Suchrichtung ändern. Standardmäßig erfolgt die Suche, wie oben erwähnt, zeilenweise. Bewegen Sie den Schalter in die Position „Nach Spalten“ können Sie die Reihenfolge festlegen, in der die Suchergebnisse beginnend mit der ersten Spalte generiert werden.

   

   In der Spalte „Suchbereich“ Es wird ermittelt, nach welchen konkreten Elementen gesucht wird. Standardmäßig handelt es sich hierbei um Formeln, also um die Daten, die beim Klicken auf eine Zelle in der Bearbeitungsleiste angezeigt werden. Dies kann ein Wort, eine Zahl oder ein Zellbezug sein. Gleichzeitig sieht das Programm bei einer Suche nur den Link und nicht das Ergebnis. Dieser Effekt wurde oben diskutiert. Um genau nach den Ergebnissen zu suchen, nach den Daten, die in der Zelle und nicht in der Bearbeitungsleiste angezeigt werden, müssen Sie den Schalter von der Position verschieben „Formeln“ in Position bringen "Werte". Darüber hinaus besteht die Möglichkeit, nach Notizen zu suchen. Stellen Sie in diesem Fall den Schalter auf Position "Anmerkungen".

   

   Durch einen Klick auf den Button können Sie eine noch genauere Suche festlegen "Format".

   

   Dadurch wird das Fenster „Zellen formatieren“ geöffnet. Hier können Sie das Format der Zellen festlegen, die an der Suche teilnehmen sollen. Sie können Einschränkungen nach Zahlenformat, Ausrichtung, Schriftart, Rahmen, Füllung und Schutz, einer dieser Optionen oder einer Kombination davon festlegen.

   

   Wenn Sie das Format einer bestimmten Zelle verwenden möchten, klicken Sie unten im Fenster auf die Schaltfläche „Verwenden Sie das Format dieser Zelle…“.

   

   Danach erscheint ein Werkzeug in Form einer Pipette. Damit können Sie die Zelle auswählen, deren Format Sie verwenden möchten.

   

   Nachdem das Suchformat konfiguriert ist, klicken Sie auf die Schaltfläche "OK".

   

   Manchmal müssen Sie nicht nach einer bestimmten Phrase suchen, sondern nach Zellen, in denen sich die Suchwörter in beliebiger Reihenfolge befinden, auch wenn sie durch andere Wörter und Symbole getrennt sind. Dann müssen diese Wörter auf beiden Seiten mit einem „*“-Zeichen hervorgehoben werden. In den Suchergebnissen werden nun alle Zellen angezeigt, die diese Wörter in beliebiger Reihenfolge enthalten.



3. Sobald die Sucheinstellungen festgelegt sind, klicken Sie auf die Schaltfläche „Alles finden“ oder "Nächstes finden" um zu den Suchergebnissen zu gelangen.

Wie Sie sehen, ist Excel ein recht einfacher, aber gleichzeitig sehr funktionaler Satz von Suchwerkzeugen. Um ein einfaches Quietschen zu erzeugen, rufen Sie einfach das Suchfenster auf, geben Sie eine Suchanfrage ein und drücken Sie die Taste. Gleichzeitig ist es aber möglich, eine individuelle Suche mit einer Vielzahl verschiedener Parameter und zusätzlichen Einstellungen zu konfigurieren.

Nehmen wir an, Sie müssen die Durchwahl eines Mitarbeiters anhand seiner Nummer ermitteln und außerdem die Provisionsquote für den Verkaufsbetrag richtig einschätzen. Sie durchsuchen Daten, um schnell und effizient bestimmte Daten in einer Liste zu finden und automatisch zu überprüfen, ob die Daten korrekt verwendet werden. Nachdem Sie die Daten angezeigt haben, können Sie Berechnungen durchführen und die Ergebnisse unter Angabe der Rückgabewerte anzeigen. Es gibt mehrere Möglichkeiten, in einer Datenliste nach Werten zu suchen und die Ergebnisse anzuzeigen.

In diesem Artikel

Suchen Sie Werte in einer Liste vertikal nach exakter Übereinstimmung

Sie können die VLOOKUP-Funktion oder eine Kombination der INDEX- und MATCH-Funktionen verwenden, um diese Aufgabe auszuführen.

Beispiele für die Verwendung der VLOOKUP-Funktion

SVERWEIS-Funktion.

Beispiele für INDEXes und MATCHES

Was bedeutet:

=INDEX(Sie müssen den Wert von C2:C10 zurückgeben, der mit MATCH(dem ersten Wert „Cabbage“ im Array B2:B10) übereinstimmt)

Die Formel sucht nach dem ersten Wert in Zelle C2:C10, der entspricht Kohl(in B7) und gibt den Wert in C7 zurück ( 100 ) – der erste entsprechende Wert Kohl.

Weitere Informationen finden Sie unter INDEX-Funktion und MATCH-Funktion.

Finden Sie Werte in einer Liste vertikal anhand der ungefähren Übereinstimmung

Verwenden Sie dazu die VLOOKUP-Funktion.

Wichtig: Stellen Sie sicher, dass die Werte in der ersten Zeile in aufsteigender Reihenfolge sortiert sind.

Im obigen Beispiel sucht die VLOOKUP-Funktion nach dem Namen eines Schülers, der 6 Verspätungen im Bereich A2:B7 hat. Es gibt keinen Eintrag in der Tabelle für 6 verspätet sich, daher sucht die VLOOKUP-Funktion nach der nächsthöheren Übereinstimmung unter 6 und findet den Wert 5, der dem Vornamen zugeordnet ist Dave und kehrt daher zurück Dave.

Weitere Informationen finden Sie unter VLOOKUP-Funktion.

Finden vertikaler Werte in einer Liste unbekannter Größe mit exakter Übereinstimmung

Um diese Aufgabe auszuführen, verwenden Sie die Funktionen OFFSET und MATCH.

Notiz: Dieser Ansatz wird verwendet, wenn sich die Daten in einem externen Datenbereich befinden, den Sie täglich aktualisieren. Sie wissen, dass Spalte B den Preis enthält, wissen aber nicht, wie viele Datenzeilen der Server zurückgibt, und die erste Spalte ist nicht alphabetisch sortiert.

C1 ist die obere linke Zelle des Bereichs (auch Startzelle genannt).

Match("Orangen"; C2: C7; 0) sucht nach oranger Farbe im Bereich C2:C7. Sie sollten die Startzelle nicht in den Bereich aufnehmen.

1 – die Anzahl der Spalten rechts von der Startzelle, für die der Rückgabewert zurückgegeben werden soll. In unserem Beispiel steht der Rückgabewert in Spalte D, Verkäufe.

Suchen Sie Werte in einer Liste horizontal nach exakter Übereinstimmung

Um diese Aufgabe auszuführen, wird die GLOOKUP-Funktion verwendet. Unten finden Sie ein Beispiel.

Die LOOKUP-Funktion durchsucht eine Spalte Verkäufe und gibt den Wert aus Zeile 5 im angegebenen Bereich zurück.

Weitere Informationen finden Sie unter LOOKUP-Funktionen.

Erstellen Sie eine Suchformel mit dem Suchassistenten (nur Excel 2007).

Notiz: Das Suchassistent-Add-In wurde in Excel 2010 eingestellt. Diese Funktionalität wurde durch den Funktionsassistenten und die verfügbare Such- und Referenzfunktion (Referenz) ersetzt.

In Excel 2007 erstellt der Suchassistent eine Suchformel basierend auf Arbeitsblattdaten mit Zeilen- und Spaltenüberschriften. Der Suchassistent hilft Ihnen, andere Werte in einer Zeile zu finden, wenn Sie den Wert in einer Spalte kennen, und umgekehrt. Der Suchassistent verwendet Index und MATCH in den von ihm erstellten Formeln.