Transkripte
1. Einführung: Wir werden über Index
- und Match-Funktionen und die leistungsstarke Kombination
beider Funktionen sprechen. Wir werden auch lernen, was eine Array-Funktion
ist. Stellen Sie sich vor,
Sie müssen in einer bestimmten Situation Daten
dynamisch aus einer riesigen Datenbank
von oben nach unten von links nach
rechts abrufen von oben nach unten von links , mit einer Genauigkeit von 100%. Was werden Sie tun? Index
and Match kann Ihnen helfen. Es kann das Einfügen von Spalten überstehen. Wenn Sie eine Spalte einfügen und weiterhin die Details zu
den Hauptdaten
hinzufügen, kann
sie trotzdem überleben. Es funktioniert wunderbar
mit der Array-Funktion. Was ist nun eine Array-Funktion? Ein Array ist Excel das
an mehrere Bedingungen gleichzeitig
denkt. Es wird für
komplexere Berechnungen verwendet. Wenn wir Array verwenden, hört
Excel auf, Zelle für
Zelle zu denken und beginnt logischer zu
denken Anstatt zu fragen, was
der Wert in dieser Zelle ist, fragt
Excel, welcher Wert meine Bedingung
erfüllt.
Das ist eine Array-Funktion In diesem Kurs werden wir
einige weitere Konzepte von
Excel kennenlernen, wie z. B. die Validierung räumlicher
Daten gegen Bezahlung , die Formatierung von
Bedingungen, das Konzept
der Sperrung der Zelle und vieles mehr.
Wir sehen uns in der Klasse.
2. Index- und Übereinstimmungsfunktionen: Zunächst werden wir uns die Grundlagen
der Index- und Match-Funktionen
einzeln ansehen , was sie bewirken. Und wenn wir es dann kombinieren, was für erstaunliche Ergebnisse können wir erzielen. Okay, lassen Sie uns mit
den Grundlagen von
Index und Match beginnen . Wie gesagt, der Index gibt
den Wert zurück und der Match
gibt die Position zurück. Lassen Sie mich nun die
Funktion als Index eingeben, und das wird
mein gesamtes Array sein. Und sagen wir, die zweite
ist die Zahl, die ich will. Die zweite Nummer, die wir haben
, ist also James. Ich werde mir die Details besorgen. Lassen Sie uns über Match sprechen und mein wertvolles
Aussehen könnte James sein, und das wird
mein gesamtes Tabellen-Array sein. Und Null für exakte Übereinstimmung. Also nochmal, ich kriege auch die
zweite Nummer als James. Sagen wir stattdessen,
wenn ich diese Zelle eintippe, geben
wir ein, und wenn ich zwei eintippe. Also hier von dieser Zelle aus kann
ich Befehle geben. Sagen wir drei, richtig? Es wird also entsprechend
ersetzt. Das ist also ein sehr
grundlegendes Verständnis von Index- und Match-Funktion. Jetzt werden wir sehen, wie magisch
es in Kombination wirken kann. Wir haben
hier Details, sagen wir,
die Details einiger
Mitarbeiter auf der linken Seite und die Details
ihrer Mitarbeiter-ID jeweils auf der rechten Seite. Ebenso haben wir Mitarbeiter-IDs auf der linken Seite und wir wollen Details auf der rechten Seite, und das muss entsprechend
übereinstimmen. Jetzt kann die Funktion „Nachschlagen“
hier nicht funktionieren , da sie
nur von links nach rechts funktioniert. Richtig, also werden wir die
Index- und Match-Funktion verwenden. Lassen Sie mich mit Index Folkson beginnen und mein Tabellen-Array wird
sein, ich will die Namen Ich gebe Namen ein, wähle Namen aus und sperre die Zelle.
Warum sperre ich? Weil ich das schleppen will. Okay. Dann lass mich die Match-Funktion
verwenden. Ich möchte die Zellen abgleichen. Bei all diesen Zellen werden
alle Zellen gesperrt und Null für exakte Übereinstimmung und beide
Klammern geschlossen und Enter eingegeben. Und lassen Sie mich das nachverfolgen
, um alle Antworten zu erhalten. Und hier sind wir mit
all den Antworten 1024 ist 1024 ist Jessica. Und 1028 ist Michael, richtig? Dementsprechend bekommen
wir das. Nun, das sind die
Systemfehler, die wir erhalten. Nehmen wir an, in einer riesigen Datenbank möchten
wir, dass unser eigener bearbeiteter, unser eigener benutzerdefinierter Fehler angezeigt wird und nicht der Systemfehler. Und wir möchten auch, dass er
hervorgehoben wird , damit wir auf die Fehler in den riesigen
Datenmengen
hinweisen können und wie wir das machen können. Für unsere persönlichen Fehler, was wir tun werden, werden wir hier im Fehlerfall
eine weitere Funktion hinzufügen . Und ich werde das noch einmal durchforsten. Anstatt den systematischen
Standardfehler von Excel zu erhalten, erhalte
ich
als meinen benutzerdefinierten Fehler „Nicht gefunden“. Und wie kann ich das hervorheben? Wir werden die bedingte
Formatierung verwenden. Wir lassen diesen Teil ausgewählt
bleiben gehen zu Bedingungsformatierung
und Text, der enthält. Nicht gefunden. Hier können wir auch die
Farbe, in der wir die Details haben möchten, bearbeiten und anpassen, sodass sie rot bleibt. Und hier sind wir, sagen wir, wenn wir das löschen, sagen
wir, wenn wir eine hypothetische Zahl schreiben, erhalten
wir den
Fehler , den benutzerdefinierten Fehler
, den wir ausgewählt haben Ich hoffe, das ist
soweit klar, welche Kombination aus Index und Match wir in einer
bestimmten Situation verwenden können Lassen Sie uns nun ein paar
Treffer wie Index mit
ein paar Bedingungen verwenden . Wir haben noch ein Beispiel. Hier stehen die Namen
der Schüler, ihre jeweiligen Fächer und ihre jeweiligen
Noten. Jetzt möchten wir, dass die
Namen der Fächer
hier angezeigt werden und wenn Sie den
Namen des Studenten auswählen, sollten
wir die Antwort erhalten. Bei den Namen der Fächer wähle
ich nun das
Fach als Steuerung C aus, kopiere und mache dann
das kostenpflichtige Sonderfach. Das nennt man Transponieren. Das ist Pay Spatial.
Es wird von vertikal nach
horizontal transponiert und umgekehrt. Ich kann
hier auch Namen eingeben, aber ich habe geschrieben. Ich habe es
als Drop-down-Liste erstellt. Ich kann zur Datenvalidierung gehen. Hier und von
hier aus kann ich
die Liste und die Quellen auswählen . Ich hoffe also, dass das
sehr viel einfacher ist. Fangen wir nun mit
Index und Match an. Jetzt wollen wir die Namen haben. Wenn Sie den Namen eines bestimmten
Schülers und das Fach
auswählen, möchten
wir, dass die jeweiligen
Noten hier erscheinen. Wir haben zwei Bedingungen. In diesem gesamten Tabellenfeld wollen
wir sowohl nach den Namen
der Schüler als auch nach ihren
Fächern suchen. Wir werden den Index und
ein paar Treffer verwenden. Sagen wir, indexieren Sie die gesamte Tabelle.
Ich werde das sperren. Warum sperre ich? Weil
ich es ziehen will. Dann möchte ich zusammenpassen. Was werde ich nun zuerst mit den Namen abgleichen? Ich werde das nochmal protokollieren. Wo erscheinen die Namen
in der zweiten Spalte? Auch hier protokolliere ich das, ich
wähle dieses Steuerelement aus und sperre es. Dann nehme ich ein anderes Match. Inzwischen habe ich
die Anpassungen für die Namen vorgenommen, aber nicht für die Fächer. Ich werde das Motiv auswählen, es
aber nicht sperren, weil ich nur vertikal ziehen möchte. Es erfolgt kein horizontales
Ziehen. Fächer zuordnen Nun, wo sind die Fächer
in der ersten Reihe? Ein 12f. Auch hier werde ich diese
Null für Exact Match sperren, ich werde beide
Klammern schließen und schon bin ich hier Wenn wir uns Saras
Geschichte ansehen, haben wir 73. Da wir jetzt
auch gesperrt haben, können wir das verschieben. Sie können alle hier
angegebenen Noten vergleichen. Lass mich den Namen in
Sima ändern und hier
erhalte ich die Namen hier. Lassen Sie mich den Namen ändern in
Ich erhalte die Namen hier. Ich kriege die
Namen hier, oder? So können wir also nur ein paar
Funktionen wie diese verwenden . So können wir die Funktion vervollständigen, indizieren und abgleichen. Wir können jetzt nach der Array-Funktion
suchen.
3. Array-Funktion: Jetzt werden wir sehen
, was ein Array ist? Was man ein Array nennt,
diese geschweifte Klammer, diese geschweifte Klammer Anstatt nach einer Funktion Enter zu
drücken und Enter zu drücken, drücken
wir Strg+Shift Enter Das ist also ein Array und es verwendet mehrere Bedingungen mit
mehreren Kriterien Schau es dir an.
Schauen wir uns jetzt Array an. Jetzt wird uns
diese Tabelle gegeben, in der wir die
Namen der Mitarbeiter hier haben, dann ihre jeweiligen
Abteilungen. Und wenn Sie genau hinschauen,
gibt es in
einigen Abteilungen gebräuchliche Namen. Nehmen wir an, wir haben Peter Gratia in AHR und einige in SAS Das muss also beachtet werden, oder? Dann erhalten wir das Gehalt, die
Leistungsvergütung und den Gesamtbetrag Was wollen wir jetzt hier? Wir wollen, dass die Namen
hier erscheinen, und wir wollen auch, dass die
Abteilungen hier erscheinen. Wenn Sie also sowohl die Namen
als auch die Abteilung auswählen, sollten
wir das Gehalt und
die Leistungsvergütung erhalten. Was nun die Namen angeht, habe ich eine Drop-down-Liste
erstellt. Und auch für die Abteilung habe ich eine Drop-down-Liste erstellt. Nun zu den Namen, da es sehr
üblich ist , dass ich zur
Datenvalidierung gehe, die Liste
auswähle und
die gesamte Quelle 2-13 Das sind die Namen, oder? Aber was werde ich
für die Abteilung tun? Weil es keine Quelle gibt. Sie haben keinen
guten Ruf. Anstatt eine Quelle auszuwählen, schreibe
ich persönlich schreibe
ich persönlich
die Namen der
Abteilungen mit Komma, sodass es auch zu einer Art
Drop-down-Liste wird , oder? Jetzt haben wir Alice
Johnson von Operations. Jetzt haben wir die
Performance der Verkäufe gewonnen. Zum Beispiel haben wir die Einzelheiten
der Verkäufe hier mit diesen
beiden Kriterien gewonnen . Was wir tun werden, beginnen wir mit der
Index- und Match-Funktion. Jetzt Array, was
es mich fragt, Array. Nun, was wird das
Array sein, das Array sollte C eins bis C 13 sein, denn das ist das Gehalt und wir
wollen das Gehalt hier haben. Jetzt werden wir Match verwenden. Jetzt haben wir zwei Treffer, die Namen und die Abteilung. Ich kann
hier Concatenate mit erster,
zweiter Bedingung und Klammer drüber verwenden , dann möchte ich das Array dieser Bedingung
nachschlagen Was werde ich tun? Für die Namen wähle
ich das gesamte
Suchfeld als Namen und äh, ich verwende und und
ich verwende Abteilung. Also für b12b 13 wird
das meine Abteilung
sein,
Null für exakte Übereinstimmung, Null für exakte Übereinstimmung, ein paar Blackett schließen,
und das ist das Array Ich möchte Strg,
Shift und Enter statt Enter drücken Shift und Enter statt Enter Drücken Sie Shift Enter und ich werde mir die Ergebnisse
ansehen. Alice Johnson von der Operation
verdient 500$ pro Gehalt. Nun, auch aus Performance-Gründen, kann
ich diese
Funktions-Array-Funktion nicht ziehen. Also auch für die Leistung verwende
ich dieselbe
Methode, Index und Match. Aber aus Performance-Gründen
wird das Array ein d12d-13-Match sein, diesen beiden
Namen und der Abteilung Schließt man die Klammer, erscheinen die
Namen 1—13 und die Abteilungen erscheinen von
B eins bis B 13,
Null für exakte Übereinstimmung, schließen mit den eckigen Klammern, Strg Shift Enter Hier erhalte ich die Details. Schauen wir uns einige andere Namen an. Nehmen wir an, wir haben Peter, wir nehmen Peter
und er ist von der Personalabteilung. Peter von der Personalabteilung
erhält also 300 als Bonus, 30$ als Zulage oder 300$
Gehalt und 30 als Zulage Und Peter aus dem Vertrieb
erhält ein anderes Gehalt. Und hier sind wir
Peter vom Vertrieb. Ebenso haben wir
gesehen, wie wir
die Array-Funktion mit
mehreren Bedingungen
und mehreren Ergebnissen ausführen können die Array-Funktion mit
mehreren Bedingungen
und mehreren . Und wenn Sie sich die Details ansehen, wissen Sie ,
ähm, die wichtigen Dokumente können niemals in KI-Tools hochgeladen
werden, oder? Wir brauchen menschliches Verständnis
und menschliche Berechnungen, und ich hoffe, ich habe Ihnen
gerade dabei geholfen, diese Funktion
zu verstehen. Bitte gehen Sie zu dem
Projekt und machen Sie es.
4. PROJEKT: Nun zum Projekt
dieser Klasse, ich habe mir diese Tabelle gegeben
und was sie hat,
sie hat einige iPhone-Namen, hypothetische zufällige Namen
ohne Modellnummer, aber das sind dann
tatsächlich Firmennamen und Poco und Opo, wenn Sie
das beobachten, Nun, wir haben die Versionen, die
auch hier angegeben sind,
die Speicherkapazität, die Menge und die Kosten.
Was will ich? Ich möchte, dass die Modellnummer hier
als Drop-down-Liste erscheint, das iPhone sollte hier erscheinen Pim Ich möchte, dass die Versionen angezeigt werden. Wir sollten alle Versionen, die sie
haben, hier erscheinen, und die Speicherkapazität sollte hier
als Drop-down-Liste erscheinen Sie werden es mit
Datenvalidierung erstellen. Was will ich sonst noch? Ich möchte, dass Sie
hier
eine Array-Funktion mit Index- und
Match-Kombination schreiben , und ich möchte, dass, wenn
ich ein Modell auswähle,
wenn ich eine Version auswähle, wenn ich einen Speicher
aus der Drop-down-Liste auswähle, wenn alle drei Bedingungen erfüllt
sind, ich möchte,
dass die Daten aus
dieser Tabelle hier sind, wie groß ist die
Menge dieser drei Bedingungen Stimmt das? Ich hoffe, das
ist ganz klar , wie Sie
vorgehen werden, um dasselbe für den Preis zu bekommen. Sie müssen
Index und Match abgleichen und herausfinden, welche Kosten entstehen wenn drei Bedingungen erfüllt
sind. Also mach es bitte selbst
und lade es in die Projektgalerie hoch. Wir
sehen uns in der nächsten Klasse.
Aamir Shaikh, Freelancer, Youtuber, Trainer
