Microsoft Excel: Lerne das Index schnell!

1. Einführung – die Formeln für den Index und die Matching: Hallo allerseits. Ich bin Jeremy, oder wie manche mich Big Dog nennen. Willkommen zu meinem Skillshare-Kurs über den Index und Match-Formeln. Bevor wir in den Inhalt einsteigen, lass mich eine Sekunde Zeit nehmen, um mich vorzustellen. Ich bin seit fast sieben Jahren in der Arbeitswelt und die ganze Zeit wurde für meine Zeit auf dem College in Excel verbracht , um meine derzeitige Rolle heute Excel war Teil meines täglichen Toolkits. Ich habe eine Leidenschaft für Analytik und Interessen entwickelt Analytik und Interessen die effizientesten Lösungen und die Liebe zur Automatisierung herauszufinden . Mein Ziel für diese Videoserie ist es, Sie zu einem Experten für den Index zu machen und Formeln abzugleichen. Die Index- und Vergleichsformeln bieten Ihnen in Kombination eine Lookup-Funktion, die die vLookup- und H-Look-ups übertrifft und sicherstellt dass sich Ihre Berechnungen aufgrund zusätzlicher Zeilen oder -Spalten kann die Indexübereinstimmung die Effektivität von Ad-hoc-Tabellen und komplexen Kostenmodellen steigern . Begleiten Sie mich in dieser Serie, während wir die nächste Stufe Ihres Zellpotenzials freischalten und die anderen Lookup-Funktionen im Staub lassen . Lass uns hinter uns her sein. 2. Grundlagen von Index und Matching – Teil 1: Genaues Match: Hallo allerseits, Willkommen zum ersten Video der Serie. In den kommenden Videos werden wir den Index und die Vergleichsformeln behandeln. Die Indexformel lässt uns einen Zellbereich definieren und ermöglicht es uns, den Wert einer Zelle aus diesem Bereich angesichts der Zellkoordinaten zurückzugeben den Wert einer Zelle aus diesem Bereich . Die Vergleichsformel fragt nach einem Nachschlagewert und dem definierten Bereich oder der Nachschlagewert wird angezeigt . Die Vergleichsformel gibt dann die Spalten- oder Zeilennummer im definierten Bereich oder in der Suche zurück Zeilennummer im definierten Bereich Wert wird gefunden. Während diese Formeln alleine verwendet werden können, ist der häufigste Anwendungsfall, wenn sie kombiniert werden. In den nächsten Videos werden wir einige Beispiele für die verschiedenen Möglichkeiten durcharbeiten , wie diese beiden Formeln verwendet werden können. Lasst uns eintauchen. In diesem Beispiel haben wir eine Rechnungstabelle für März, in der die Kundenstadt fehlt. Wir möchten die Kundenstadt von der Nachschlagetabelle in den Arbeitstisch ziehen . Wir können den kombinierten Index und die Vergleichsformeln verwenden , um uns zu helfen. Die Indexformel fragt zunächst nach dem IRB-Strahl, der unsere gewünschten Daten enthält , nämlich B3 bis B6 in der Nachschlagetabelle. Beachten Sie, dass dies als absolute Referenz festgelegt wird , damit der Bereich nicht ändert, wenn wir die Formel in andere Zellen kopieren. Als Nächstes will die Indexformel die Zeilennummer in dem Array , das wir gerade für die Zelle definiert haben, in der sich unsere gewünschten Daten befinden. Weil sich die spezifische Zeilennummer für die Stadt , die wir zurückgeben möchten, je nach Kunden ändert. Wir brauchen die Zeilennummer, um dynamisch zu sein. Dafür verwenden wir die Vergleichsformel. Die Vergleichsformel fragt zuerst nach dem Nachschlagewert eine Zehn. Als Nächstes werden wir aufgefordert, das Lookup-Array einzugeben, das A3 bis eine Sechs sein wird. Genau wie das obige Index-Array wird dies auch auf eine absolute Referenz gesetzt. Schließlich wird nach dem Match-Typ gefragt, in diesem Fall 0 sein wird, da wir eine genaue Übereinstimmung weniger als und größer als Matches in späteren Videos erklären möchten eine genaue Übereinstimmung weniger als und größer . Aber wisse, dass diese normalerweise nur zum Abgleichen von Zahlen verwendet werden. Die Indexformel hat eine endgültige optionale Eingabespaltennummer , die wir in diesem Beispiel nicht verwenden werden, aber wir werden uns später einen Blick darauf werfen. Okay, wir haben die Formel beendet. Lassen Sie es uns kopieren und sehen, ob wir die richtigen Städte bekommen. Fantastisch. Da diese Formelkombination etwas verwirrend sein kann, lassen Sie uns schnell überprüfen, was passiert ist. Zunächst verwenden wir die Indexformel, um den Zellbereich zu ermitteln, in dem sich die gewünschten Kundenstadtinformationen befinden. Da wir die Zeilennummer der Kundenstadt für jede Rechnungsposition nicht manuell eingeben wollten , verwenden wir die Vergleichsformel, um die Zeilennummer dynamisch zurückzugeben. Die Vergleichsformel gibt die Zeilennummer des Kunden in der Nachschlagetabelle basierend auf dem Nachschlagewert zurück, den wir aus der Arbeitstabelle angegeben Wir konnten die Zeilennummer verwenden, die der entspricht der Formel, die als Eingabe in die Indexformel wird , um dann die Kundenstadt zurückzugeben. Denken Sie daran, dass die Indexformeln oder das Ray- und das Nachschlag-Array für Vergleichsformeln dieselben Zeilenwerte aufweisen müssen. In unserem Fall die Reihen drei bis sechs, damit die Formeln ordnungsgemäß zusammenarbeiten. Okay, lassen Sie uns zu einem anderen Beispiel übergehen , um unser Verständnis zu festigen. 3. Grundlagen von Index und Matching – Teil 2: Weniger als Match: Hallo allerseits, Willkommen zum zweiten Video der Serie. In diesem Video werden wir auf der Kombination von Index- und Match-Formeln aufbauen und tiefer in das eintauchen, was die Vergleichsformel leisten kann. Lasst uns darauf eingehen. In diesem Beispiel haben wir eine Liste der Kunden und die von jedem erzielten Jahresumsatz. Für jeden Kunden möchten wir bestimmen, welche Rabatte auf den Rabattrännen basieren sollten , die mit den jährlichen Umsatzzielen in der Nachschlagetabelle verbunden sind. Wie im vorherigen Video beginnt die Indexformel mit dem Array der gewünschten Daten, B3 bis B8. Um als Nächstes die Indexformel Zeilennummer Input Dynamic ist, müssen wir die Vergleichsformel verwenden. Im vorherigen Videobeispiel verwenden wir den Kunden als Lookup-Wert und 0 eine exakte Übereinstimmung als Übereinstimmungstyp. In diesem Beispiel verwenden wir eine Zahl, Jahresumsatz als Lookup-Wert in einem weniger als Übereinstimmungstyp. Lasst uns das durchgehen. Der Übereinstimmungstyp wird der jährliche Umsatz zwischen dem Lookup-Array drei bis acht sein, und der Übereinstimmungstyp wird eins sein. Lassen Sie uns schnell durchgehen, was in der Spielformel passiert. Diesmal nimmt die Matratze zuerst den jährlichen Umsatznachschlagswert an und passt sie an den Jahresumsatz im Lookup-Array an. Da wir weniger als den Übereinstimmungstyp eingegeben haben, wird die Vergleichsformel den nächstgelegenen Wert im Lookup-Array finden den nächstgelegenen Wert im , der kleiner als unser Nachschlagewert ist. Es gibt dann die Zeilennummer des nächstgelegenen weniger als Übereinstimmung und wird als dynamische Zeilennummer in die Indexformel eingespeist . Wir hatten bereits die Indexformel, das Array der Daten, angegeben . Und jetzt gibt es mit einer dynamischen Zeilennummer aus der Vergleichsformel den Inhalt der Zelle an der Position der dynamischen Zeilennummern innerhalb des Arrays zurück . Lassen Sie uns die Formel nach unten kopieren, um zu sehen ob wir die erwarteten Ergebnisse erhalten haben. Großartig, alles sieht gut aus. Gehen wir weiter zum nächsten Beispiel. 4. Grundlagen von Index und Match: Teil 3: Match Match: Hallo allerseits, Willkommen zum dritten Video der Serie. In diesem Video fügen wir Kombination aus Index- und Vergleichsformeln und zusätzlicher Vergleichsformel eine zusätzliche Ebene Index- und Vergleichsformeln und zusätzlicher Vergleichsformel hinzu. In den letzten beiden Beispielen haben wir uns angeschaut, nur die Zeile Nummer zwei zurückzugeben, die Indexformel, um die gewünschten Daten zu erhalten. In diesem Beispiel werden wir verwenden, um Formeln abzugleichen, um sowohl die Zeilen- als auch die Spaltennummern zurückzugeben. Dies ermöglicht es uns, eine größere Bandbreite von Daten mit nur ein bisschen mehr Arbeit im Voraus zurückzugeben . Los geht's. In diesem Beispiel haben wir eine Rechnungstabelle für März, in der Kunden, die Stadt, das Bundesland und die Postleitzahl fehlen . Wir verwenden die Informationen aus der Nachschlagetabelle, um unsere Arbeitstabelle zu füllen. Im Gegensatz zu den beiden anderen Beispielvideos beginnen wir mit einem Indexarray, das Zeilen und Spalten enthält , B3 bis D6. Das Indexarray wird sowohl Zeilen als auch Spalten gesperrt. Eine wichtige Anmerkung hier wird es in dieser Formel sehr wichtig sein, auf die Referenztypen der Zellen zu achten . Einige Zellen haben die Zeilen und Spalten in manchen gesperrt , wir haben nur Zeilen oder Spalten gesperrt. Wenn der falsche Zellreferenztyp verwendet wird, die Formel nicht das zurück, was wir beabsichtigt haben. Als Nächstes verwenden wir die erste Vergleichsformel, um die Zeilennummer dynamisch zurückzugeben. Der Nachschlagewert der ersten Übereinstimmungsformeln ist der Kunde ein Zehn. Dieser Nachschlagewert hat die Spalte gesperrt, aber die Zeile zehn wird geöffnet, das Lookup-Array wird A3 bis A6 sein , wobei sowohl Zeilen als auch Spalten gesperrt sind. Schließlich ist der Übereinstimmungstyp 0, da wir eine exakte Übereinstimmung wünschen. Damit die zweite Übereinstimmungsformel die Spaltennummer zurückgibt. Der Nachschlagewert ist der Ort der Spaltenüberschrift. In Zelle C9. In dieser Zelle wird die Zeile neun gesperrt, aber die Spalten C werden geöffnet. Das Lookup-Array wird B2 bis D2 sein, wobei sowohl die Zeilen als auch die Spalten gesperrt sind. Schließlich ist der Übereinstimmungstyp wieder 0, da wir eine exakte Übereinstimmung wünschen. Kopieren wir diese Formel in die restlichen Zellen in der Spalte „Stadt“ sowie in die Spalten des Bundesstaates und der Postleitzahl. Süß, alles funktioniert wie erwartet. Lassen Sie uns ein wenig sichern und darüber sprechen, wie diese Formel funktioniert. Wir begannen mit der Indexübereinstimmungsformel, indem wir den Zellbereich angegeben haben, der die Daten enthält , die wir schließlich zurückgeben möchten. Eine kurze Anmerkung: Wir möchten sicher sein, dass dieser Zellbereich nur Daten enthält , die wir zurückgeben möchten, und keine Nachschlagewerte enthält, die wir zum Abrufen der Daten verwenden werden. Okay? wir nun den Zellbereich haben, der unsere Rückgabedaten enthält, zu finden, benötigt die Indexformel jetzt sowohl die Zeilennummer die Spaltennummer, in der die Daten gespeichert sind. Anstatt statische Zahlen wie Zeile eins und zweite Spalte anzugeben , um Ohio zurückzugeben, verwenden wir Vergleichsformeln um diese Werte dynamisch zurückzugeben. Wir müssen die Koordinaten nicht manuell eingeben. Die erste Übereinstimmungsformel wird die Zeilennummer dynamisch zurückgeben . Wir geben die Formel, den Nachschlagewert, den Kunden im Zellbereich an, in dem der Nachschlagewert des Kunden enthalten ist. Und es gibt die Zeile des Kunden-Lookup-Wertes zurück. Da jeder Kunde in der Nachschlagetabelle entsprechende Informationen zu Stadt, Bundesland und Postleitzahl hat . Die Daten, die wir wollen, geben die Zeilennummer zurück, wo die Übereinstimmungsformel gefunden wurde. Der Kunde gibt uns auch die Zeilennummer der Daten , die wir zurückgeben möchten. Die zweite Übereinstimmungsformel wird die Spaltennummer dynamisch zurückgeben. Wir geben die Formel, den Nachschlagewert, die Spaltenüberschrift und den Zellbereich an, dem die Spaltenüberschrift entspricht. Und es wird die Position der Spaltenüberschrift als Zahl zurückgeben , die beide Koordinaten aus den beiden Übereinstimmungswerten angegeben , die beide Koordinaten aus den beiden Übereinstimmungswerten Die Indexformel kann nun den Wert der Zelle zurückgeben, nach der wir gesucht haben. Das waren ziemlich viele Informationen, die man aufnehmen musste, aber Sie haben es durchgemacht. Da dies das letzte Video der Serie ist, möchte ich mich bei Ihnen für Ihr Anschauen bedanken. Wenn Ihnen dieser Kurs gefällt, nehmen Sie sich bitte eine Minute Zeit, um sich meine anderen Kurse anzusehen. Und wie immer, wenn Sie sich festfühlen, können Sie gerne zurückhalten und eine Frage stellen. Danke.