Excel PRO TIPS Teil 3: Formeln

1. Formula Einführung: Alles klar, willkommen zu einem meiner Lieblings-Abschnitte Formel Tipps. Jetzt ist dies einer der anspruchsvolleren und technischen Teile des Kurses und deckt Themen ab, die von grundlegenden wie Berechnungsmodi und Auditing-Tools bis hin zu ziemlich fortgeschrittenen, wie viele zu viele Nachschlagen und einige Produkte zu erhöhen. Denken Sie daran, dass sich sehr schnell bewegen wird, so dass es schwierig sein kann, Schritt zu halten. Wenn Sie nicht schon mit den Grundlagen vertraut sind, tauchen wir ein. 2. Manuelle vs. automatische Berechnung: Alles klar. nächste Tipp ist ein Formel-Tipp. Dies ist ein einsternes, ziemlich grundlegendes Konzept, aber es ist etwas, das nicht jeder Excel Benutzer bewusst ist. Und es ist, wie Sie Ihre Formel ändern. Berechnungsmodi Excel verfügt jetzt über zwei primäre Berechnungsmodi. Automatisch, das ist in der Regel Ihre Standard- und Bedienungsanleitung. Wenn Sie also auf die Registerkarte „Formel“ nach rechts gehen, sehen Sie Ihre Berechnungsoptionen. Es gibt eigentlich drei verschiedene Möglichkeiten. Sie sind die beiden, auf die wir uns jetzt konzentrieren werden. Sind automatisch, das erlaubt Ihre formalistische Berechnung auf eigene Faust mit jeder Arbeit der Änderung und manuelle, die im Wesentlichen friert diese Formelberechnungen, bis Sie sich entscheiden, eine neu berechnen sie aktualisieren . Und Sie können dies tun, indem Sie entweder auf die Schaltfläche Jetzt berechnen in der Registerkarte Formel klicken oder die Verknüpfung F neun verwenden. Nun, die dritte Option automatisch, mit Ausnahme von Datentabellen, werden wir über Datentabellen im analytischen Abschnitt dieses Kurses sprechen. Diese werden oft verwendet, um eine große Anzahl von Simulationen zu randomisieren und auszuführen. Es ist ein Teil von Exzellenzen. Was ist, wenn Analysetools und weil sie im Allgemeinen über eine sehr, sehr große Anzahl von Zeilen iterierten , können sie die Dinge verlangsamen, wenn sie ständig neu berechnen. Also, diese dritte Option gibt Ihnen im Grunde die Wahl zu sagen, Sie wissen, alle meine anderen Arbeitsmappenformeln als automatisch zu behandeln. Aber lassen Sie mich entscheiden, wann ich diese Datentabellen speziell aktualisieren möchte. Nehmen wir an, Sie haben Ihren Berechnungsmodus auf manuell eingestellt, wie wir hier sehen, und dann schreiben Sie eine Art Funktion wie diese. Dies ist die Prophetenformel, die wir in dieser Demo schreiben werden und im Grunde die Menge, den Verkaufspreis und die Stückkosten berücksichtigt Menge, . Sie ziehen diese Formel nach unten, wenden sie auf mehrere Zellen an und sehen in jedem Fall den gleichen Wert zurück. Der erste Ort, um zu schauen, ist der Berechnungsmodus, denn neun Mal von 10 bedeutet dies, dass es versehentlich auf manuell umgeschaltet wurde. Aber es gibt gute Neuigkeiten. Es ist eine sehr einfache Lösung. Alles, was Sie tun, ist, gehen Sie zurück zu diesem Tab , überprüfen Sie die automatische Option, und dann werden diese Formeln automatisch von selbst neu berechnet. Um einige häufige Anwendungsfälle zusammenzufassen, haben Sie in einigen Fällen möglicherweise eine sehr große Arbeitsmappe, sehr formelschwer, mit Dingen wie Nachschlage- und Index- und Referenzfunktionen oder einer Erhöhung, die viele iteriert, Vielfaches vorbei. Eine große Anzahl von Zeilen In den Fällen, in denen die Berechnungen eine ganze Weile dauern, können Sie vorübergehend in den manuellen Modus wechseln, während Sie andere Arbeitsmappenbearbeitungen vornehmen . Nur um der Geschwindigkeit und Effizienz willen, Stellen Sie sicher, dass Sie es wieder auf Automatisch ändern, wenn Sie möchten, dass diese Formeln neu berechnet werden. Der zweite übliche Anwendungsfall. Wieder, wie ich schon sagte, erlaubt es Ihren Formeln, automatisch selbst zu berechnen. Behandeln Sie Ihre Datentabellen jedoch getrennt. Also lassen Sie uns in eine Pro-Tip-Arbeitsmappe springen und ich werde Ihnen zeigen, wie das aussieht. In Ordnung, also aus unserem Inhaltsverzeichnis, werde ich hier in unsere grüne Kategorie oder Formel-Tipp schauen. Erste Artikel Berechnungsmodi gehen voran und verknüpfen Sie mit dieser Stadt, und hier haben wir eine Probe von etwa 100 Produkten zusammen mit verkaufter Menge. Sie haben den Verkaufspreis und die Stückkosten für jedes dieser Produkte, und wir möchten hier nur den Umsatzgewinn berechnen. Bevor ich das tue,springen wir in ihren Formeln Tab. Bevor ich das tue, Drilldown in unsere Berechnungsoptionen. Sie werden sehen, dass meine Arbeitsmappe standardmäßig auf „Automatisch“ eingestellt ist. Gehen Sie voran und nur um der Demonstrationen willen, schalten Sie dies auf Handbuch und dann direkt aus oder funktionieren Sie hier. Also unsere Umsatzgewinn-Formel, es wird die Menge mal der Preis minus die Kosten sagen wir, gleich B zweimal offene Klammern. Einzelhandelspreis abzüglich der Stückkosten. Schließ es aus. Drücken Sie die Eingabetaste Das gibt $12.058,25 zurück, was der richtige Wert für Straße zu ist. Aber genau wie ich Ihnen gezeigt habe, wenn wir das ganz unten anwenden, werden wir denselben doppelten Wiederholungswert sehen , der offensichtlich falsch ist. Also werde ich zurück zu Formeln Berechnungsoptionen automatisch zu tun. Und da gehst du, alles. Berechnet es neu, um die Formel oder so etwas neu zu schreiben, und wir sind gut zu gehen. Also wieder, sehr einfaches Konzept, aber wirklich hilfreich. Eine, die Sie beachten sollten, sobald Sie wissen, dass diese beiden Berechnungsmodi existieren 3. Formula: In diesem Pro-Tipp geht es darum, Formelzeilenumbrüche zu verwenden, was eine gute Möglichkeit ist, die Lesbarkeit zu verbessern und Ihre Formeln zu organisieren. Jetzt ist es ein Einstern-Tipp sehr, sehr einfach. Aber es ist etwas, das mir eigentlich seit einiger Zeit nicht bewusst war. Und die Idee ist, dass, während Sie tatsächlich in der Excel Formelleiste eingeben oder bearbeiten anstatt nur drücken, eingeben, eingeben, die Ihre Formel einreichen wird, Alz zuerst halten, so dass, wenn Sie ault drücken und geben Sie Ihre Erstellen Sie einen Zeilenumbruch und teilen Sie Ihre Formel in mehrere Zeilen auf. Um Ihnen also ein Beispiel in dieser Demo zu geben, die durchlaufen wird, haben wir diese verschachtelte if-Anweisung, die nicht extrem lang ist. Aber es ist ziemlich lang, und was wir tun werden, ist, dass Formel die Zeilenumbrüche verwendet, um jede Bedingung auszubrechen , jede einzelne if-Anweisung in ihren eigenen Rogen. Und das ändert nichts daran, wie die Formel berechnet oder die Ergebnisse, die sie produziert. Alles, was es tut, ist, die Organisation und Lesbarkeit zu verbessern. Also sehr unkompliziertes Zeug hier. Häufige Anwendungsfälle, Funktionen, insbesondere wie diese, in diese mehrere Zeilen aufteilen , um einzelne Kriterien zu isolieren oder komplexere verschachtelte Formeln einfacher zu interpretieren, indem Einführung jeder der neuen Komponentenfunktionen in einer neuen Zeile. So kommen Sie auf den Punkt, wenn Sie sehr fortgeschritten mit Formeln und Funktionen, wo Sie alle möglichen Dinge wie Offset und Index und Count Day und Rose kombinieren. Und wenn Sie alle innerhalb einer einzigen endlosen Reihe von Funktionen sind, können sie sehr, sehr schwer zu interpretieren und zu diagnostizieren sein . Irgendetwas läuft schief. Also lasst uns in Excel springen und diesem eine Chance geben. In Ordnung, also aus unserem Inhaltsverzeichnis, suche ich nach der Formelzeile, bricht Demo und gehe weiter und drücke Link, und das wird dich zu einem Tab bringen, der Informationen über 24 verschiedene Geschäfte oder Supermärkte. Ich habe eine Region i D. Sie haben City State, Land, Land, und dann diese Total Square Foot Spalte, die diese Formel in Spalte H antreibt und das ist eine verschachtelte if-Anweisung, wir sind mehrere Kriterien zu testen und im Wesentlichen eine Art von Etikett auf der Grundlage der Gesamtquadratfuß anzuwenden, so dass je nach Größe des Ladens, waren es als klein, mittel, groß oder sehr groß zu kennzeichnen mittel, . Das wird also ein guter Kandidat für diese Ault-Zeilenumbrüche hier in der Formelleiste sein. Klicken wir nun in die Formelleiste. Als erstes können wir den Mauszeiger über den unteren Rand bewegen, bis Sie sehen, dass zwei Punkt einen Pfeil klicken und halten und ziehen Sie ihn nach unten. Das wird uns nur ein bisschen mehr Platz geben, mit dem wir innerhalb der Bearbeitungsleiste arbeiten können. Und jetzt wollen wir Zeilenumbrüche zwischen jeder der if-Anweisungen, jeder der einzelnen Bedingungen oder Kriterien, die getestet wurden, erstellen . Platzieren Sie also einfach Ihren Cursor direkt davor. Erstens, wenn nach dem Komma und ALTs gedrückt halten, während Sie die Eingabetaste drücken, wird das voran gehen und den Ansturm bewegen. Diese Formel bis in die nächste Zeile. Genau das Gleiche hier vor dem nächsten. If-Anweisung Ault geben und dann noch einmal für das Finale. If-Anweisung Ault eingeben. So können wir jetzt viel deutlicher sehen. Okay, wir testen die Werte in G, um im ersten Test zu schreiben. Ist die Quadratmeterzahl größer als 35.000? Wenn ja, werden wir den Laden sehr groß beschriften. Wenn das nicht wahr ist, werden wir in die nächste Zeile bewegen, die größer als 30.000 ist. Wenn ja, beschriften Sie groß. Und das ist eine Art, wie diese verschachtelten, wenn Anweisungen funktionieren, ist, dass sie durch eine Reihe von echten falschen Tests fortschreiten . Und dann, wenn keiner von diesen Luft wahr ist, haben Sie diese Art von Fang alle Wert falsch. Und ich nehme an, wir könnten das auch auf seinen eigenen Rogen fallen lassen, denn das ist das Endergebnis. Das endgültige Etikett, das andere ist. Und da haben Sie nicht wir drücken, geben Sie ein und wenden Sie das unten an. Nichts sollte sich ändern, mit Ausnahme der Tatsache, dass wir unsere Formel ein bisschen lesbarer, ein bisschen benutzerfreundlicher gemacht haben . Also, da Sie gehen, indem Sie Zeilenumbrüche Ihre Formeln mit allen eingeben. 4. Konvertieren von Einheiten der Messeinheiten: Diese nächste Demo ist eine, über die ich wirklich aufgeregt bin, weil es eine Funktion ist, die ich gerade erst vor ein paar Monaten zum ersten Mal entdeckt habe. Namen Convert können wir über das Ändern Ihrer Maßeinheiten mit dieser Konvertierungsfunktion sprechen Also Formel wirkt genau so, wie es klingt, können Sie Werte von einer Maßeinheit in eine andere ändern . Sie haben also möglicherweise Daten, die in Pfund liegen und müssen in Kilogramm umgerechnet werden. Vielleicht haben Sie Temperaturwerte und Celsius, und Sie möchten, dass Fahrenheit gegeben gilt, konvertieren Sie in Datums- oder Zeitfelder, indem Sie Minuten in Sekunden konvertieren, ohne mit 60 multiplizieren zu müssen oder zusätzliche Konstanten in Ihre Formeln hinzufügen. Also werden wir dort ein paar verschiedene Demos sprechen, von denen einer Durchschnittstemperaturen sein wird, die in Fahrenheit aufgezeichnet werden. Und was wir tun werden, ist einfach diese Convert-Funktion zu verwenden. Sie sehen eine Box mit all Ihren Optionen, und was wichtig zu beachten ist, ist, dass Sie nicht jedes Mal die vollständige Liste der Maßeinheiten sehen , wenn diese Liste dynamisch sein wird, und es hängt von Ihrer von Einheit ab. Also in diesem Fall, die einzigen Optionen, die ich sehe, da ich Fahrenheit eingegeben habe. Temperatureinheiten sind Celsius, Fahrenheit Kelvin und die anderen, mit denen ich nicht vertraut bin, aber ihre temperaturspezifischen Optionen. So ist es eine einfache ist die Auswahl der Option, die Sie in diesem Fall wollen, das See-Label für Celsius. Und da gehst du hin. Sie haben eine neue konvertierte berechnete Spalte erstellt. So ziemlich unkomplizierte Anwendungsfälle hier. Nummer Eins. Machen Sie diese schnellen Konvertierungen ohne Nachschlagen von Konversionsraten oder Taschenrechnern, und zweitens, konvertieren Sie datum- oder zeitspezifische Werte, ohne Formelkonstanten hinzufügen zu müssen. Lassen Sie uns in unsere Pro-Tipps Arbeitsmappe springen und einige Conversions machen. Also aus Ihrem Inhaltsverzeichnis schauten sich ihre Formel Tipp Abschnitt hier an. Suchen Sie nach der Demo für die Messkonvertierung Drücken Sie Link, und wenn Sie auf diesem Blatt landen, werden Sie zwei verschiedene Tabellen sehen. Daten haben hier durchschnittliche monatliche Temperaturen, die in Bezug auf Fahrenheit Spalte B aufgezeichnet werden. Wir werden konvertieren verwenden, um diese Fahrenheit Werte in Celsius umzuwandeln, und wir haben auch einige Individuen hier mit Größen- und Gewichtsmetriken, und wir haben Größe in Zoll und Gewicht in Pfund, nur weil die USA gerne Menschen verwirren und nicht das metrische System benutzen, also werden wir diese Zoll in zwei Zentimetern drehen, waren wiederum die Pfund in Kilogramm . Also lasst uns die Temperatur in Celsius beginnen, wir werden mit einem gleich konvertieren Open der Prinz ist die Zahl, die wir Leben hier umwandeln wollen und drei sein, und wir werden von Fahrenheit konvertieren. Wenn Sie also das Label kennen, können Sie einfach f in Anführungszeichen eingeben oder durchsuchen. ehrlich zu sein, ich bin mir nicht sicher, in welcher Reihenfolge diese, Aaron und ich wissen keinen schnellen Weg zu suchen, aber hier ist es irgendwie in der Mitte ein bisschen nach unten. Dann können Sie auf diese Option doppelklicken oder eine Tabulatortaste klicken, um sie in die Formel zu ziehen. Und dann ist das letzte Argument, was konvertierst du Fahrenheit in? Und jetzt, wenigstens schrumpft es diese Liste dynamisch im Schlepptau auf Lee, die temperaturspezifischen Optionen. So Celsius ist derjenige, den sie brauchen Diesmal alle Tablette in der Nähe, die Klammer und drücken Sie OK, und da haben Sie es. So sind 41,7 Grad Fahrenheit etwa 5,39 Grad Celsius. Und ich habe diese mit einem Konvertierungsrechner überprüft, nur um sicherzustellen, aber Sie können das gleiche tun , wenn Sie möchten und doppelklicken und ihre vor. Mit einem Klick haben wir alle unsere Fahrenheit Temperaturen in Celsius umgewandelt . Gehen wir nun zu Höhen und Gewichten, also fangen wir mit der Höhe an. Wir werden Zentimeter in zwei Zentimetern umrechnen. So gleich konvertieren. Wir wandeln F drei von Zoll um. Suchen Sie ein bisschen danach. Da ist es, ich m Tablette in und die Einheit, die wir wollen diese Zoll zu gut umwandeln. Hier sind die Optionen. Haben Sie Meter, Meilen? Seemeilen zurück in Zoll, Fuß, Yards, Angstrom. Aber Sie werden feststellen, dass wir hier keine Zentimeter sehen. Fakt. Die nächste Option, die wir haben, sind Meter, also könnten Sie versucht sein zu sagen, Ordnung, na ja, ich habe kein Glück. Lassen Sie uns Meter hineinfallen, schließen Sie es ab und multiplizieren Sie dann mit 100, um diese in Zentimeter zu verwandeln, was Ihnen die richtige Antwort geben wird. 83 Zoll ist 210,2 Zentimeter. Aber hier ist die Sache, die ich entdeckt habe, ist, dass selbst wenn die Option in vielen Fällen nicht existiert, die Eingabe der Abkürzung unabhängig funktioniert. Also werde ich sagen, Hör zu , ich will keine Meter, die ich will Zentimeter, die universell bemerkt wird, wie cm das in setzen, Zitate schließen meine Formel drücken Sie die Eingabetaste und sie werden die richtige Antwort bekommen, ohne führen Sie diese Konstante bei der Multiplikation mit 100 in unsere Formel ein. Also, da gehst du, wende es nach unten an. Und jetzt, sehr ähnlichen Fall werden wir uns mit der Umwandlung von Pfund zwei Kilogramm gleich befassen, konvertieren die Zahl, die seine Zelle h drei Wir gehen von Pfund, Mrs. Pfund Mass El bien Doppelklick, und wir wandeln es in Kilogramm um, die nicht auf dieser Liste leben. Wir haben Gramm. So wie zuvor könnte eine Option sein, Gramm zu verwenden und dann durch 1000 geteilt, um in Kilogramm zu verwandeln. Oder wir können einfach sagen, Hey, das Symbol für Kilogramm ist K g. Schließen Sie die Klammer. Da gehst du hin. Wir bekommen die richtige Antwort. £260 ist gleich knapp 118 Kilogramm. Also gibt es ein paar Demos dafür, wie Sie diese super praktische Convert-Funktion verwenden können, um Ihre Maßeinheiten zu ändern 5. Echtzeit-Tools mit HEUTE UND JETZT: Alles klar. Diese nächste Demo ist eine lustige. Wir werden heute und jetzt volatile Funktionen verwenden, um unseren Arbeitsmappen in Echtzeit formlos hinzuzufügen , so dass heute und jetzt Formeln im Grunde nur das aktuelle Datum und die aktuelle Uhrzeit zurückgegeben werden. Und da es sich um volatile Funktionen handelt, bedeutet dies, dass sie automatisch mit jeder Änderung der Arbeitsmappe neu berechnen. , Wenn Sie aus irgendeinem Grund nicht möchten,dass sie sich ändern, möchten Sie nur das aktuelle feste Datum oder die aktuelle Uhrzeit stempeln. Sie können die Steuerelement-Semikolon-Verknüpfung verwenden, um den aktuellen Tag zurückzugeben, oder den Semikolon der Steuerverschiebung , um die aktuelle Zeit zurückzugeben. Und anders als heute und jetzt Funktionen, werden diese nur hart codiert bleiben. Diese werden sich nicht dynamisch ändern. Jetzt für diese Demo werden wir ein paar ziemlich schnelle Projekt-Pacing-Tools bauen. Und im ersten Fall werden wir eine Heute Funktion verwenden, um einen Projekt-Tracker basierend auf den vergangenen Tagen und den verbleibenden Tagen zu erstellen . Und dann verwenden wir eine Funktion jetzt, um ein ähnliches Tool zu erstellen, das auf vergangenen Stunden und verbleibenden Stunden für kürzere Projekte basiert . Nun, häufige Anwendungsfälle. Jedes Mal, wenn Sie das aktuelle Datum oder die aktuelle Uhrzeit in einer Arbeitsblattzelle anzeigen möchten, können Sie heute oder jetzt verwenden und Werkzeuge wie diese erstellen, z. B. Zeitplan- oder Zeitplanwerkzeuge, die Sie basierend auf dem aktuellen Tag oder der aktuellen Uhrzeit aktualisieren möchten. Also lassen Sie uns in Excel springen und tatsächlich üben, mit diesen heutigen und heutigen Funktionen zu arbeiten. Alles klar, Also aus Ihrem Inhaltsverzeichnis Tab, suchen Sie nach, dass heute und jetzt Demo in Ihrer Formel Tipps Abschnitt und gehen Sie vor und drücken Sie Link um zu diesem grünen Tab zu springen. Und von hier aus sehen Sie, dass ich eine leere Platzhaltervorlage für diese beiden Projekt-Tracker-Tools erstellt habe, die wir erstellen werden, und die 1. 1 wird auf der heutigen Funktion basieren. Also wählen Sie Zelle C vier, wenn wir nur Kontrollsemi Colon verwenden würden. Diese Verknüpfung wird am aktuellen Tag fallen, und der aktuelle Tag ist korrekt. Es ist 16. Oktober, aber das Problem ist, wenn ich morgen oder nächste Woche wiederkomme, wird dieser Wert weiterhin 10 16 lesen. Es ist nicht dynamisch. Es ist statisch und hart codiert. Und das ist nicht das, was ich hier will, weil wir ein Tool entwickeln wollen, das dynamisch ist, das sich basierend auf dem aktuellen Tag und der aktuellen Zeit ändert . Aus diesem Grund werde ich die heutige Funktion verwenden, und ich muss nicht einmal Argumente eingeben. Sie öffnen und schließen einfach die Klammern, drücken Sie die Eingabetaste. Und da gehst du hin. Jetzt haben wir den gleichen Wert. 10 16 2018. Aber es wird sich jeden Tag ändern, wenn ich auf diese Arbeitsmappe zugreife. Also habe ich in einem Beispiel, Start-, Datums- und Enddatum hier fallen gelassen Start-, . Sie können diese Werte so ändern, wie Sie es für richtig halten, wenn Sie dies erkunden und spielen möchten. Nun, eine Sache, die wichtig ist, über Excel Daten zu wissen, ist, dass jedes Datum, genau wie dieses, einen zugrunde liegenden Datumswert hat. Das ist eine ganze Zahl. Und diese Zahl repräsentiert die Anzahl der Tage, die seit überragt Beginn der Zeit, nur 1. Januar 1900 vergangen sind. Und da jedes Datum durch diesen ganzen Zahlendatumswert unterstützt wird, bedeutet dies, dass wir einfache mathematische und statistische Operationen gegen Datumszellen anwenden können , um Dinge wie Zeitintelligenzfunktionen zu tun. Um Ihnen ein Beispiel dafür zu geben, wenn wir die Projektlänge in Tagen berechnen möchten, können wir einfach das Fälligkeitsdatum und das Startdatum subtrahieren. Das wird uns sagen, dass es uns gut geht. 728 Tage in diesem gesamten Projekt. Zeitachse. Nun, die gleiche Logik für die anderen Formeln. Anzahl der Tage, die vergangen sind. Nun, das ist nur das heutige Datum, abzüglich des Projekts Startdatum C vier minus C sechs und die verbleibenden Tage sind ein einfaches wie das Fälligkeitsdatum minus dem heutigen Datum C sieben minus C vier. Wenn Sie OK drücken, werden Sie sehen, dass dieses Donut-Diagramm gefüllt wird, was uns jetzt sagt. Okay, Sie sind 288 Tage in diesem Projekt, das 40% der gesamten Projektzeitachse darstellt. Gute Möglichkeit, einen Puls auf den Fortschritt zu halten, vor allem für langfristige Projekte wie dieses. Nun, die eine Optimierung, die ich hier machen werde, ist, dass, wenn wir ein Fälligkeitsdatum bevölkert haben, das bereits wie der 1. Oktober 2018 vergangen ist, Sie werden einige seltsame Sachen sehen. Wir bekommen noch negative Tage, die auf dem Chart gezeichnet werden und irgendwie seltsame Art und Weise, das zu verhindern. Lassen Sie uns voran und Modifikatorformel hier gehen und verkaufen. Siehe 11 und ich werde den Cursor direkt nach dem Gleichheitszeichen platzieren und if-Anweisung hinzugefügt um einfach zu sagen, ob das heutige Datum größer ist als das Fälligkeitsdatum. Das Fälligkeitsdatum ist bereits in einem Komma auf unseren Wert übergegangen. Wenn das wahr ist, und wenn die Werte wahr sind, wenn das Fälligkeitsdatum abgelaufen ist, dann geben wir einfach eine Null ein, weil es keine Tage mehr in diesem Projekt gibt , die noch einmal zum Wert von false. Sonst werden wir einfach unsere Formel C sieben minus C vier wie zuvor ausführen. Schließen Sie die Prinzessin, drücken Sie die Eingabe. Und da gehst du jetzt, oder transplantieren Art von Kappen bei 100%. Und wir können voran gehen und hinzufügen, was Datum Sie in der Zukunft wollen, und tun 11. 25 2019 zum Beispiel , Dieser Fall war 42% des Weges durch dieses Projekt. Das ist also ein gutes Beispiel dafür, wie die Today Funktion mit ganzen Zahlenberechnungen verwendet wird. Jetzt werden wir ein bisschen granularer werden und üben, auch mit Daten und Zeiten zu arbeiten . Und weil wir die Datumszeit wollen, nicht nur das Datum als ganze Zahl, werden wir die Now-Funktion wieder verwenden. Keine Formelargumente öffnen sich einfach. Schließen Sie diese Klammern, drücken Sie die Eingabetaste und Sie werden sehen, dass es mir einen Wert von 10 16 2018 15 42 gibt. Das ist militärische Zeit für 15 42 Uhr und bedenken Sie daran, dass das mir nicht nur die Zeitkomponente gibt. Es gibt mir das Datum und die Uhrzeit zusammen. Also habe ich genau wie vorher einige anfängliche Einschränkungen für dieses Projekt eingegeben. Wir beginnen die Zeit von 9 Uhr morgens. Machen Sie Zeit von 17 Uhr. Also haben wir es mit einer viel kürzeren Zeitleiste hier innerhalb eines einzigen Tages zu tun . Und die einzige Sache, die ich tun muss, bevor es vorwärts gehen kann, ist tatsächlich nur die Zeit von diesem jetzt zu entfernen . Sonst werde ich keine direkten Vergleiche mit diesen Werten machen können. 9 Uhr und 17 Uhr. Also hier, in Zelle C 21, möchte ich, dass diese Zelle nur die Zeitkomponente dieses Datums zurückgibt. Ich werde Ihnen zwei Möglichkeiten zeigen, das zu tun. Die erste ist die Verwendung von herausragenden Zeitfunktion, und es sucht nach einer Stunde in einer Minute Eingabe und einem zweiten Eingang, und ich werde diese Eingaben mit Zeitfunktionen bekommen und auf meine jetzt zeigen. Also die Zeit, die ich zurückkehren möchte, basiert auf Strom unser, die die Stunde von jetzt die aktuelle Minute ist, die die Minute von jetzt ist, und die aktuelle Sekunde, die die aktuelle Sekunde von jetzt ist. Schließen Sie die Klammern, drücken Sie die Mitte. Das übersetzt die gesamte Datumszeitzeichenfolge in nur den Zeitmesser nur um 15 44 Uhr, was genau das ist, was ich jetzt will. Es gibt einen schnelleren Weg, dies zu tun, aber es erfordert ein wenig tieferes Verständnis dafür, wie diese Datums- und Uhrzeitfunktionen und Datumswerte wirklich funktionieren. Die Quintessenz ist, dass einzelne Tage und Excel wie ganze Zahlen behandelt werden, oder? Wenn ich mit der rechten Maustaste auf dieses Datum klicke, verkaufe 10 16 2018 Format, die Zellen gehen in die allgemeine Kategorie. Das ist eine gute Möglichkeit, zu sehen, wie der tatsächliche zugrunde liegende Datumswert aussieht. 43389 Wieder, das ist die Anzahl der Tage, die seit dem 1. Januar 1900 vergangen sind. Beachten Sie, dass es eine ganze Zahl ist, wenn ich das vergleiche, also ist das Abbrechen, damit wir dieses Format nicht anwenden. Vergleichen wir das mit der Now-Funktion, die sich erinnern, ist der aktuelle Tag, plus der Zeitmesser als auch. Kannst du erraten, wie das aussehen wird? Geh im Allgemeinen. Beachten Sie, dass es sich um denselben Datumswert mit zusätzlichen Werten nach dem Dezimalkomma handelt. Und das liegt daran, dass Zeiten als Bruchteile von Tagen und Bruchteilen von Tagen behandelt werden. In Bezug auf das Datum sehen Werte nur wie Bruchteile ganzer Zahlen aus. Wenn wir also nur den Zeitmesser wollen, der nur die 65577 nach der ganzen Zahl wäre, müssen wir nur buchstäblich eine Formel schreiben, die die jetzt Berechnung nimmt und den Tag subtrahiert , der die ganze Zahl, und Sie sind mit nur dem Rest, der eine Minute vergangen ist links . Also, jetzt ist es 3 46 PM Etwas einfacher Weg, es zu tun, aber wieder erfordert es diese Art von tieferem Verständnis von Datumswerten. Okay, zurück zum Projekt, alles, was wir jetzt tun müssen, um das Ziel zu bringen, ist die Projektlänge in Stunden zu berechnen , was einfach die Fälligkeit minus zwei Startzeit ist. 19 Meilen, 18 Stunden vergangen ist unsere aktuelle Zeit abzüglich der Startzeit. Dies ist genau wie die Datumsberechnungen, die wir gerade oben gemacht haben und dann nicht zuletzt werden die verbleibenden Stunden die Fälligkeit minus der aktuellen Zeit C 19 minus C 21 Drücken Sie die Eingabetaste und da gehen wir. Also begann unser Projekt um neun Uhr. AM Es wird um fünf enden. Derzeit sind es 3 46, was bedeutet, dass ich nur noch eine Stunde und 13 Minuten auf 85% des Weges übrig habe. Durch diese Zeitachse werde ich besser knacken, wenn ich dieses Projekt jetzt beenden möchte. Letzt Zu guterLetztmöchten wir die gleiche if-Anweisung Änderung erstellen, um für Fälle zu steuern, in denen wir die fällige Zeit bereits verstrichen haben . Wir können das mit der exakt gleichen Methodik hier tun. Wenn unsere aktuelle Zeit in C 21 größer ist als die fällige Zeit. Wenn wir die gegebene Zeit verstrichen sind, bleiben keine Stunden in ihrem Projekt übrig. Andernfalls berechnen Sie C 19 minus C 21. Das war also ein sehr schneller Crashkurs in Excel Datumswerten und heute und jetzt Funktionen. Aber hoffentlich gibt Ihnen das einige gute Ideen, wie Sie diese flüchtigen Funktionen verwenden können , um Ihre eigenen Echtzeit-Berechnungen zu erstellen. 6. Formula: In diesem nächsten Tipp geht es darum, Fehler in Ihrem Arbeitsblatt mithilfe von Tools zur Formelüberwachung zu finden und zu beheben . nun der Registerkarte „Formeln finden Sienuneine Gruppe einzelner Werkzeuge unter der Kategorie „Formelüberwachung“, die Ihnen dabei helfen sollen , Referenzen zu verfolgen, Ihre Formel, Berechnungen zu bewerten und letztendlich Diagnose und beheben Sie alle Fehler in Ihrem Arbeitsblatt. Also werden wir in dieser Demo über all diese verschiedenen Optionen sprechen. Wir beginnen mit der Verfolgung der Prioritätsunabhängigkeit, die im Grunde Pfeile auf alle Zellen zeichnet, die sich entweder auf den ausgewählten Wert auswirken oder von ihm betroffen sind , ähnlich wie eine übergeordnete untergeordnete Beziehung. Und hier ist ein Beispiel dafür, wie das in diesem Fall aussieht, das dort in unserer Demo sprechen wird. Wir verfolgen die Priorität, die sich auf diese Bargeldwerte auswirkt, auf Schließ- und monatliche Aufwandswerte , damit wir verstehen können, welche Eingabezellen sich auf diese Werte oder diese Ausgänge auswirken. Wir haben auch eine Reihe von anderen Tools, über die wir reden werden Formeln zeigen, wird einfach vorübergehend alle Arbeitsblatt-Formeln als Text anzeigen, und dann wird die Fehlerüberprüfung das Blatt auf Fehler scannen, uns helfen, die Quelle zurück zu irgendwelche Präzedenzzellen. Und schließlich, mein persönlicher Favorit, das Evaluierungsformel Tool, das es uns ermöglicht, jede einzelne Komponente einer Funktion oder Formel Schritt für Schritt zu bewerten . Und das ist großartig, um festzustellen, wo eine Formel brechen könnte, besonders wenn Sie eine sehr komplexe Formel oder eine Reihe von verschachtelten Funktionen haben. Also eine Menge praktischer Anwendungsfälle hier für einen, einfach zu verstehen, wie einige dieser komplexen Formeln und Funktionen Betriebsnummer zwei sein könnten . Visualisierung, die verkauft Faktor in eine bestimmte Formel, Ausgabe oder Verkauf, und dann drei. Tracing, Diagnose und hoffentlich Behebung der Quelle von Fehlern, auf die Sie stoßen, so dass wir in die Demo springen. Lassen Sie uns in unserer Pro-Tip-Arbeitsmappe die Arbeit mit einigen dieser Auditing-Tools üben . In Ordnung, wenn Sie also folgen, gehen Sie weiter und öffnen Sie Ihre Pro-Tip-Arbeitsmappe. Suchen Sie im Abschnitt Formeltipps nach der Demo für Formelüberwachungstools und gehen Sie weiter und drücken Sie Links, um direkt zu diesem Blatt zu springen. Und was wir hier betrachten, ist ein Immobilienrechner. Es ist ein Grundmodell, das ich tatsächlich verwendet habe, um Immobilien- und Kreditkosten zu bewerten, und im Wesentlichen, was hier los ist, ist, dass Sie einige grundlegende Informationen über eine Immobilie haben ,einen Kaufpreis und einen Steuersatz haben. , Sie können hier einige Kreditbedingungen eingeben, wie eine Anzahlung und Zinssatz und einen Begriff wie Und diese Werte werden eine Reihe von Berechnungen durchlaufen und letztendlich das Geld ausspucken, das benötigt wird, um auf dem Grundstück zu schließen und eine Schätzung für monatliche Ausgaben. Aber offensichtlich sind wir nicht hier, um über Hypothekenzahlungen und Immobilienkosten zu sprechen. Im Moment sind wir hier, um über die Bewertung von Formeln zu sprechen. Also lassen Sie uns in unsere Registerkarte Formeln gehen. Werfen Sie einen Blick auf unsere Formula Auditing-Gruppe, die die Tools enthält, über die wir gesprochen haben. Und beginnen wir damit, über Vorrang und Abhängigkeit zu sprechen, weil es sehr kompliziert klingt . Es ist eigentlich ziemlich einfach, und eine Möglichkeit, darüber nachzudenken, ist nachzudenken für diesen monatlichen Kostenwert die Zelle, die ich ausgewählt habe, das ist das Ende des Berechnungsflusses. Diese monatliche Aufwandsnummer füttert keine anderen Formeln ein. Von diesem monatlichen Aufwandswert sind keine anderen Ausgabezellen betroffen. Mit anderen Worten, diese Zelle hat keine abhängigen Zellen, und ich kann das durch Klicken auf Ablaufverfolgungsabhängigkeit beweisen, und ich werde diese Art von Warnmeldung erhalten, die besagt, dass es keine Formeln gibt, die sich auf diese aktive Zelle beziehen . Aber es gibt Zellen, natürlich, dass Service-Eingänge zu diesem monatlichen Kostenwert. Mit anderen Worten, es gibt Zellen. Dieser Akt hat Vorrang vor der ausgewählten Zelle. Und um diesen Präzedenzfällen zu zeigen, muss ich nur auf diesen Knopf klicken und es wird sagen, ja, ja, diese fünf Werte hier H 14 bis H 18 wirken sich alle auf diese monatliche Kostenzahl aus. Mit anderen Worten, wenn Sie einen dieser Werte ändern, ändern sich auch die monatlichen Ausgaben. Dieselbe Geschichte hier, mit Bargeld zu schließen, gibt es keine Abhängigkeit. Dies ist das Ende des Berechnungsflusses, aber es gibt eine Reihe von Vorrang, so dass der Cash-to-Close-Wert eine Funktion des Kaufpreises, der Anzahlung und der geschätzten Abschlusskosten ist . Nun, andere Möglichkeit, eine ähnliche Geschichte hier zu erzählen, können wir die Araber entfernen, besteht darin, eine Formel auszuwählen,zu Formel auszuwählen, verkaufen und entweder in die Bearbeitungsleiste zu klicken oder das F zum Bearbeiten zu verwenden. Und was das tun wird. Sie erhalten die Pfeile nicht, aber Sie werden immer noch ausgewählte Zellen sehen, die in Ihrer Formel referenziert werden, und was mir diesem Ansatz gefällt, ist, dass sie ebenfalls farbcodiert sind, so dass Sie sie irgendwie dem einzelnen Komponenten innerhalb dieser Formel, nur noch ein weiteres Werkzeug, das Sie in Ihre Gesäßtasche stecken können, um Ihnen bei der Diagnose zu helfen und Ihre Formeln zu verstehen. Also gehen Sie weiter, drücken Sie die Eingabetaste, und wir haben Vorrang von diesen Zellen zurückverfolgt. Aber auch die gegenteilige Beziehung gilt, so dass wir wissen, dass Bargeld zum Schließen vom Kaufpreis abhängig war. Daher ist der Kaufpreis ein Präzedenzfall, um Bargeld zu schließen, und wir können das zeigen, indem wir die Abhängigkeit von dieser Zelle verfolgen. Und jetzt, was uns das zeigt, ist, dass, wenn wir den Kaufpreis ändern, es gibt 1234 fünf Zellen oder Ausgänge, die sich dadurch ändern werden. Es gibt fünf abhängige Zellen basierend auf diesem Wert und, ah, ein cooler Tipp, den ich erst vor kurzem entdeckt habe, ist, dass dies nur den ersten Schritt des Berechnungspfades zeigt , wenn wiederum einige dieser abhängigen Zellen haben auch zusätzliche Abhängigkeit von dort, so Kaufpreisauswirkungen, Kreditbetrag, Abschlusskosten und Grundsteuer. Aber wenn wir jetzt wieder auf Trace-Abhängigkeit klicken, können wir sehen, dass es hier eine andere Ebene gibt, in der der Grundsteuerwert, wie wir gezeigt haben, auch diese monatliche Ausgabe beeinflusst. Jetzt sehen wir eine Art der vollständigen Kette von Ereignissen und den vollständigen Berechnungsfluss über mehrere Schritte. Wirklich leistungsfähiges Werkzeug. Gehen Sie weiter und entfernen Sie die Pfeile. Ich zeige Ihnen, wie das Werkzeug „Formeln anzeigen“ aussieht. Ziemlich unkompliziert. Es dehnt sich nur aus, so dass es die tatsächlichen Formeln zeigen kann. Gefragt Text Art von alle Seite an Seite. Gute Art zu vergleichen. Sie wissen, welche Angebote in einem Blatt Konstanten sind und welche Formeln sind, und könnten diese Ehre dann einfach ausschalten. Jetzt auf meine Lieblingsoption, das Auswerteformelwerkzeug. Wählen wir hier eine dieser Formeln aus, Ähm, Ähm, so etwas wie die Grundsteuer. Klicken Sie auf Formel auswerten. Es wird dieses Dialogfeld aufrufen und Sie sehen gleich von Anfang an genau die Formel, die Sie in Ihrer Bearbeitungsleiste sehen. Aber was Sie jetzt tun können, ist tatsächlich auf die Unterstreichungskomponente zu schauen, wenn Sie hier nur bewerten klicken , dass die zugrunde liegende Komponente ausgewertet wird. Also sagte es nur, dass H drei auf 499.000 bewertet, weil H drei Kaufpreis und dann, wenn das geteilt wird durch 1000, die bewertet 4 99 Dann, wir durchgehen, können wir den Prozess fortsetzen. H vier ist 9,5, 9,5 mal für 99. Diese 47 40.5, geteilt durch 12 gibt uns, dass 3 95 monatliche Grundsteuer Zahlung. Also wieder, Gute Möglichkeit, Ihre Formeln auf einer tieferen Ebene nicht zu verstehen. Und es gibt eine weitere Funktion zu dieser Auswertungsformel. Werkzeug, das hilfreich sein kann. Zeigen Sie, dass wir zu unserem Bargeld gehen, um die Formel zu schließen, und wir werden diese bewerten. Und es ist ziemlich einfach. Formel H dreimal H sieben plus H 11 h drei ist für 99 h sieben Dieser Punkt, um zusammen zu multiplizieren . Es sind 99 800. Hier ist die Sache, die wir zu H 11 kommen. H 11 enthält einen Wert von 99 80, aber das 99 80 ist kein statischer, hartcodierter Wert. Es ist selbst aus einer anderen Funktion produziert. Dieser Schritt-in-Button ermöglicht es Ihnen, OK zu sagen, jede 11 ist ein wenig komplizierter. Mal sehen, wie H 11 selbst produziert wird und indem wir eine Ebene tiefer gehen, indem wir eine Art von einer weiteren Schicht der Zwiebel zurückziehen , können wir jetzt sehen, dass H 11 als H dreimal berechnet wird 30.2 diese Formel Art von verschachtelt innerhalb unserer ursprünglichen eine und dann treten Sie zurück und sagen, OK, so kommen wir zu den 99 80. Dann, wenn wir sie addieren, kommen wir zu unserem Endprodukt, das 1097 80. Also da hast du es. Das ist eine Evaluierungsformel. Offensichtlich sieht alles viel einfacher und einfacher aus, wenn die Dinge gut liefen. Lassen Sie uns also eine schnelle Probe durchlaufen, wenn die Dinge möglicherweise nicht so funktionieren, wie Sie es erwarten würden. Nehmen wir also an, wir wollen Anzahlung Prozent von 10% eingeben, aber unser Finger rutscht Peitschen und wir tippen 10 p Prozent. Weißt du, offensichtlich stimmt hier etwas nicht. Wir haben vier Zellen, die jetzt einen Wertfehler ausspucken. Dies ist ein guter Zeitpunkt, um dieses Fehlerüberprüfungs-Tool hier zu verwenden, das tatsächlich durch Ihr Blatt läuft und sagen wird, Hey, wir haben eine Reihe von Fehlern gefunden. Die ersten in Zelle J neun. Klicken Sie auf „Weiter“. Es ist eine andere in h 10 Hat eine in jedem 14 eins in J drei und dann haben Sie eine Reihe von Optionen hier für jeden der Fehler. Ah, Hilfe zu diesem Fehler wird Sie auf die Office-Support-Website bringen. Berechnungsschritte zeigen führen Sie tatsächlich zum Werkzeug „Formel auswerten“ und speziell zu dem Schritt, der den Fehler ergibt. Und das hier wird mir zeigen, dass wir versuchen, 499.000 zu multiplizieren, was ein Wert mit dieser Textzeichenfolge ist , umgeben von Anführungszeichen. 10 p Prozentsatz, der zu diesem Wert Fehler auswertet, offensichtlich, wie Sie es durchtragen, wird nur einen endgültigen Fehler am Ende des Tages ergeben. So ist es geschlossen, dass eine andere Möglichkeit, dass Sie dieses Fehlerüberprüfungstool verwenden können, wenn wir zum nächsten Fehler gehen . Was ist es? Beendet es. Dies ist die, die wir wollen Monatliche Ausgaben. Gehen Sie zurück und Fehlerüberprüfung. Manchmal erhalten Sie diese Option tatsächlich den Fehler als auch verfolgt. Und wenn Sie das tun, wird es diese Präzedenzpfeile mit einem Unterschied füllen. Es wird den Pfeil rot in der Phase drehen, wo die Fehler stattfinden, und das ist ein schöner Weg, um die Dinge zurück zu verfolgen und zu sagen OK, Schritt eins ist in Ordnung. Schritt zwei ist in Ordnung. Punkt, an dem Schritt drei ausgewertet werden soll. Dort findet ein Fehler statt. Also weiß ich, dass es der Eingabewert direkt vor Schritt drei ist, der mein Problem verursacht, das in diesem Fall verkaufen H sieben ist. Es ist dieser vermasselte Anzahlungsprozentsatz, und wir haben das Problem isoliert, damit wir das erledigen können. Wir können unsere Pfeile entfernen und wir können diesen Prozentsatz auf 10% fixieren und drücken Sie die Eingabetaste und alles ist Ordnung mit der Welt. So viele Werkzeuge gibt es eine Menge zu decken, aber wirklich hilfreiche wertvolle Optionen, um in Ihrer Gesäßtasche zu halten, wenn Sie mit Formeln und Funktionen arbeiten und Excel. 7. Pivot-Style mit Formeln: Alles klar. In diesem Pro-Tipp geht es darum, Statistiken, Funktionen und speziell bedingte Statistikfunktionen zu verwenden , um Pivot-Style-Berichte zu generieren. Du fragst dich also vielleicht, wovon ich über ein paar Wennens rede. Zählen. Wenn Durchschnitt ifs, diese bedingten Statistiken Funktionen, die es Ihnen ermöglichen, Daten zusammenzufassen, gegeben eine bestimmte Bedingung oder eine Reihe von Bedingungen. Und wenn Sie darüber nachdenken, funktioniert genau so Pivot-Tabelle, wo diese Bedingungen durch Zeilenbeschriftungen, Spaltenbeschriftungen und Ihre Filter definiert werden. Um Ihnen ein Beispiel zu geben, werden wir uns eine Teilmenge dieser I M D B Filmdaten hier und Spalten A bis F ansehen und was wir haben, ist eine kleine Pivot-Tabellenansicht wie diese, die im Grunde nach Genre gleich Aktion gefiltert wird. Wir haben die fünf besten Länder nach Titelanzahl, und wir fassen hier drei verschiedene Metriken oder Werte zusammen. Wir haben die Anzahl der Titel entstanden. Wir haben den Teil der Umsatzspalte, und wir haben den Durchschnitt der IMDB-Score-Spalte, und die Idee hier ist, so etwas zu produzieren, was im Wesentlichen genau dasselbe Tabellenlayout und dieselbe Funktionalität repliziert. Onley verwendet Zellformeln außerhalb der Pivot-Tabellenumgebung. Um das zu tun, werden wir diese treuen bedingten Statistiken verwenden. Funktionen, erneuert zählen, wenn zu berechnen Titel zählen einige ifs, um den Umsatz und Durchschnitt ifs zu berechnen, um die durchschnittliche IMDb Score zu berechnen . Jetzt fragen Sie sich vielleicht, ob Sie es in einem Pivot tun könnten, ohne eine Formel zu schreiben. Warum würdest du all diese zusätzliche Arbeit durchmachen und im Grunde das Rad von Grund auf neu erstellen ? Also meine Annahme ist, dass Pivots ein großartiges Werkzeug für eine Art unstrukturierte unguided Analyse sind, was bedeutet, dass sie großartig sind, wenn Sie nicht wirklich wissen, was Sie suchen und Sie wollen nur schneiden und würfeln und Dinge zu erkunden Zehe. Erfahren Sie mehr über Ihre Daten. andererseits wissen, Wenn Sieandererseits wissen,wie Sie Ihre Daten segmentieren möchten, und Sie wissen, welche Werte Sie als Teil eines Berichts oder Dashboards anzeigen möchten , können Sie dies mithilfe dieser Statistikfunktionen innerhalb von Arbeitsmappenzellen tun, , die Ihnen viel mehr Flexibilität geben, Ihren Bericht genau nach Ihren Wünschen zu entwerfen und zu formatieren und zu gestalten , insbesondere im Vergleich zum Erstellen eines Pivot-Style-Berichts mit Dingen wie Slicern und Pivot-Diagrammen , die auch groß aus ihren eigenen Gründen. Also die Anwendungsfälle hier, in denen ich mich auf unser Entwerfen von benutzerdefinierten formatierten dynamischen Berichten konzentrieren werde , ohne Pivots zu verwenden und Ihre Rohdaten basierend auf einem bestimmten Satz von Kriterien zu filtern oder zu segmentieren , was genau diese Funktionen sind entworfen, um zu tun. Lassen Sie uns also weiter in unsere Demo in unserer Pro-Tip-Arbeitsmappe gehen und sehen, ob wir das funktionieren können. Okay, also aus Ihrem Inhaltsverzeichnis, werden Sie nach den Pivot-Style-Berichten suchen Demo die Neustart-IP. Dies ist eine moderate Schwierigkeit, so dass es hilft, ein wenig Hintergrund in diesen Statistiken Funktionen zu haben. Ah, aber selbst wenn du es nicht tust, wird es hoffentlich in der Lage sein, ziemlich genau mitzuverfolgen. Lassen Sie uns eine Verknüpfung zu diesem Arbeitsblatt. Und genau wie ich hier beschrieben habe, haben wir eine Teilmenge der IMDB-Daten. Es hat etwa 3700 Titel, und die Spalten, mit denen wir arbeiten müssen, sind die Titel selbst, das Genre Sprache Land. Ich bin DB-Score Umsatz und Budget. Und hier haben wir eine Pivot-Tabelle für Rick Tools ändern Datenquelle gehen. Sie werden sehen, dass dieser Pivot tatsächlich mit unseren Daten und Spalten A bis G verbunden ist. Drücken Sie OK, und wieder haben wir hier ist Ah, Schnellansicht, die die Anzahl der Titel zeigt. Ein Teil der Einnahmen und der durchschnittliche IMDB-Wert werden speziell nach Aktionstiteln gefiltert. Und wir haben einen Wertefilter, um die fünf wichtigsten Elemente oder in diesem Fall Länder basierend auf dem Konto des Titels anzuzeigen . Also drücken Sie OK und hier haben gerade ein wenig ah hinzugefügt, Platzhalter leere Vorlage, wo wir diese bedingten Statistiken Formeln verwenden werden, um im Grunde die genauen Werte zu replizieren, die wir hier oben sehen. Also habe ich gerade kopiert, in die gleichen Ländernamen fallen gelassen. Ich habe hier eine Zelle hinzugefügt, die nur das Wort Aktion enthält. Moment könnten Sie das in eine Datenvalidierung umwandeln. Drop down, wenn du willst. Alles, was wir hier tun werden, ist zu versuchen, diese Werte zu replizieren, also beginnend mit der Zählung des Titels sind offensichtlich Zusammenfassungen. Der ationsmodus ist hier eine Zählung. Aus diesem Grund werden wir eine Zählfunktion verwenden, und weil wir nach zwei Kriterien testen, möchten wir Zeilen unter zwei Fällen zählen. Eines, wo das Land U. S. A.entspricht S. A. und wo das Genre Aktion ist, weil wir mehrere Bedingungen haben. Wir werden die Pluralversion dieser gezählten Funktion namens Count Ifs verwenden, und gehen Sie vor und öffnen Sie die Klammer. Und hier ist, wo wir diese Formel füttern. Diese Kriterienpaare und Kriterienbereiche. Unsere ersten Kriterien sind also Land. Es gibt Länderwerte Lebendige Spalte d Drücken Sie F vier, um es zu sperren und die Kriterien, nach denen wir suchen, was wir versuchen, innerhalb dieser Spalte D in diesem Fall zu filtern, ist das Land namens USA. Wir können diesen Verwandten in diesem Fall verlassen, oder Sie können mit F vier fahren, bis Sie Ihre Spalte gesperrt haben, weil die Länderlabels werden immer hier in Spalte I leben. Aber wir wollen, dass die Straße nach Großbritannien verschoben und stieg 17 Frankreich und Reihe 18 und so weiter und so weiter. Und das ist vorbei zu unserem nächsten Criteria Range, das ist ein Genre Range lebt hier und Spalte B F vier, um es in den Kriterien für das Genre zu sperren , lebt hier und verkauft J 13 und es wird immer hier in J 13 leben, so dass wir korrigieren Sie die gesamte Referenz. Schließen Sie die Klammer, drücken Sie die Eingabetaste. Und da gehst du. 700 zu Titeln oder stieg für uns. Eine Action-Filme, die Übereinstimmungen sind Pivot-Tabelle oben. Und weil wir unsere Referenztypen richtig eingestellt haben, können wir diesen Wert einfach nach unten ziehen und 69 27 24 14 erhalten. Also haben wir einen Streichhölzer. Da ist alles gut. Wir werden hier einen sehr, sehr ähnlichen Ansatz verfolgen,um die Einnahmen zu berechnen. Wir werden hier einen sehr, sehr ähnlichen Ansatz verfolgen, Außer, dass wir dieses Mal nicht zählen. Wir fassen so genau die gleiche Logik zusammen. Wir werden ein paar Wennens benutzen. Und der einzige Unterschied besteht darin, mit den einigen Bereichswerten zu beginnen, die wir addieren möchten . Leben Sie hier in Spalte F die Umsatzwerte. Und das würde nur diese Kriterien, Bereiche und Werte eingeben , wie wir es vorher getan haben. Also erste Kriterien Land und D Kriterien ist U S A Block. Das zweite Kriterium, das ich beziehe, ist das Genre und sein, und das letzte Kriterium ist die Aktion in J 13. Sperren Sie es in schließen Sie die Klammer drücken Sie die Eingabetaste. Da gehst du. $59 Milliarden wenden es nach unten. 3.991 Punkt 766 49 8 43 Boom. Wir haben einen Streichhölzer. Wir haben im Wesentlichen gerade die Arbeit einer Pivot-Tabelle mit diesen bedingten Statistikfunktionen erledigt. Nun, last but not least, wollen wir den Durchschnitt der IMDb-Punktzahl. Also hast du es erraten. Diesmal werden wir durchschnittliche Wennenswerte verwenden. Gleiche wie bei einigen Wenn's. Außer, anstelle einer gewissen Reichweite, werden wir eine durchschnittliche Reichweite erreichen. Dieser Fall, der e ist. Gehen Sie durch den gleichen Prozess. Kriterien reichen von einem Land. Welche USA-Kriterien reichen zu Genre. Und wir wollen Aktion sperren, schließen Sie es aus drücken Sie die Eingabetaste und ziehen Sie es nach unten. 623656 Den ganzen Weg runter bis 648 Und da hast du es. Und jetzt, da diese bedingten Statistikfunktionen auf Zellen zeigen, die Benutzer tatsächlich aktualisieren oder ändern können , bedeutet das, dass unsere Werte in diesem kleinen Bericht, den wir erstellt haben, völlig dynamisch sind, da diese bedingten Statistikfunktionen auf Zellen zeigen, die Benutzer tatsächlich aktualisieren oder ändern können, bedeutet das,dass unsere Werte in diesem kleinen Bericht, den wir erstellt haben, völlig dynamisch sind, wie ein Drehpunkt. Wenn Sie hier beispielsweise beispielsweise auf Abenteuerfilme gefiltert haben, könnten Sie Ihre Kriterien Cell J 13 in Abenteuer ändern. Und da gehst du. Sie erhalten die gleichen übereinstimmenden Werte. Also haben wir fast unsere eigene kleine Pivot-Tabelle erstellt, um oder Pivot-Stil Bericht. unsere eigene kleine Pivot-Tabelle erstellt, Zumindest mit diesen bedingten Statistiken, Funktionen wie count If Summit und Durchschnittswerte wirklich hilfreiches Tool, vor allem, wenn Sie Berichte oder Dashboards erstellen müssen, um Ihre Daten zusammenzufassen und ein wenig mehr Flexibilität in Bezug auf das Layout und das Design. 8. Wörter in einer Zelle zählen: Alles klar, Dieser nächste Profi-Tipp ist einer meiner Lieblings-Tipps. Es ist eine wirklich clevere Möglichkeit, Textformeln in Excel zu kombinieren, um die Anzahl der Wörter innerhalb einer Zelle zu zählen . Und jeder, der mit Textfunktionen vertraut ist, hat wahrscheinlich von der Länge oder L E N Formel gehört , die im Wesentlichen nur die Anzahl der Zeichen in einer Wortzeichenfolge oder einer Textzeichenfolge berechnet . Und das ist hilfreich, aber nicht ganz das, was wir hier brauchen, da wir versuchen, ganze Wörter zu berechnen. Und um das zu tun, was wir tun müssen, müssen wir hier ein bisschen kreativ werden und einige zusätzliche Funktionen wie Ersatz und Trim kombinieren , um das zu bekommen, was wir brauchen. Also für unsere Demo werden wir uns einige Weinverkostungsdaten ansehen, wo wir diese Verkostungsbeschreibungen in Spalte Fhaben Spalte F und unser Ziel ist es, einen Wortzähler in Spalte G zu erstellen, der zählt. Die Worte aus Spalte F Simple lautet, dass das, was jetzt enden wird, etwas ist, das irgendwie so aussieht , was ein bisschen wie ein Tier ist. Es macht vielleicht auf den ersten Blick nicht viel Sinn. Keine Sorge, ich werde Sie durch jeden einzelnen Schritt dieser Funktion führen, bis Sie genau verstehen wie es jetzt funktioniert. Anwendungsfälle hier, offensichtlich, offensichtlich, wie wir reden über das Hinzufügen von Zählern in Fällen, in denen Wortzahlen wichtig sind, oder Analyse von Textfeldern Dinge wie Tweets Blog-Such-Anzeigenkopie auf was auch immer könnte auf der Länge basieren . So viele verschiedene praktische Orte, an denen Sie diesen Tipp verwenden könnten. Nun lassen Sie uns in unsere Pro-Tipps Arbeitsmappe springen und tatsächlich einen eigenen Wortzähler bauen. Okay, also suchen wir nach der Demo zum Zählen von Wörtern in einem unserer grünen Tabs. Wenn Sie sich in Ihrem Inhaltsverzeichnis befinden, gehen Sie weiter und drücken Sie Link, und wir springen direkt zur Registerkarte. Und genau hier haben wir unsere Weinverkostungsdaten. Wir betrachten den Weinnamen und die Spalte A. Die Sorte und Spalte B haben die einzelnen Schnuppernamen hier in Spalte C und Colin D ist das, was uns wichtig ist. Gibt es eine tatsächliche schriftliche Beschreibung von dort Verkostung und was wir tun möchten, ist, diese Textfunktionen hier in Spalte e zu verwenden , um die Anzahl der Wörter in diesen Beschreibungen zu berechnen. Also, was ich tun werde, ist, das Stück für Stück zu zerlegen, und dann werden wir die Stücke zusammenbauen, um zu schaffen, was wir brauchen. Vielleicht ist das einfachste Einzelstück die Eliane oder Längenfunktion und zeigt Ihnen, was das tut. Wir nehmen die Länge der Zelle D zwei mit einem einfachen ist, dass Sie nur auf eine Zelle zeigen etwas Textdrücken enthält, eingeben und 58 zurückgeben, was nicht die Wortzahl ist. Es ist die Anzahl der Charaktere. Also behalte die Nummer hinten. Dein Verstand. Diese Originalbeschreibung enthält insgesamt 58 Zeichen. Nun, das nächste Stück, das wir benutzen werden, ist ein Formel-Kult-Ersatz. Ich werde diese Längenfunktion einfach mit Ersatz überschreiben. Ich habe es immer schwer, diese aus irgendeinem Grund zu buchstabieren. Und die Art und Weise, wie diese Ersatzfunktion funktioniert, gehen wir. Wir können ein bisschen besser sehen, ist, dass wir wieder auf unser Textfeld zeigen. Rechts D zu. Und ich sagte: Was in der ursprünglichen Textzeichenfolge suchen wir zu ersetzen? Was ist der ältere Originaltext? Dies könnte ein individueller Charakter sein. Es könnte ein ganzes Wort sein, eine ganze Reihe von Wörtern in diesem Fall, was wir eigentlich ersetzen wollen, ist ein Leerzeichen. Also werde ich ein Anführungszeichen an einem Leerzeichen öffnen und es abschließen und dann zum nächsten Argument kommentieren , was okay ist, das ist die Textur. Suchen Sie jetzt? Was möchten Sie diesen Raum ersetzen? Und in diesem Fall wird es hier irgendwie ein bisschen clever. Richtig ist, dass wir diesen Raum eigentlich nicht durch irgendetwas ersetzen wollen. Wir wollen den Raum vollständig loswerden und Excel sagen. Das ist es, was wir wollen. Ich werde im Grunde zwei aufeinanderfolgende Anführungszeichen eingeben , genau wie ich es getan habe, aber ohne das Leerzeichen dazwischen und weil wir jede Instanz dieser Leerzeichen ersetzen oder ersetzen wollen , genau wie ich es getan habe, aber ohne das Leerzeichen dazwischen und weil wir jede Instanz dieser Leerzeichen ersetzen oder ersetzen wollen, wirklich kümmern sich um dieses letzte optionale Argument. Die Instanznummer. Wir schließen einfach das Pressezentrum in Klammern und was Sie mit so etwas enden . Beeren- und Kirscharomen waren überraschend robust und sauber. Es ist die Textzeichenfolge, die wir ursprünglich in D zwei ohne Leerzeichen hatten. Wenn wir also fortfahren und diese Formel ändern, klicken Sie direkt nach dem Gleichheitszeichen. Was ist, wenn wir die Länge dieser neuen modifizierten Zeichenfolge nehmen, die wir am Ende geschlossen Auszubildende erstellt Drücken Sie geben 50. Okay, also wissen wir, dass die ursprüngliche Saite 58 war. Jetzt wissen wir, dass die Version der Zeichenfolge ohne Leerzeichen 50. Indem wir die Differenz dieser Zahlen nehmen, können wir im Wesentlichen die Anzahl der Leerzeichen zählen, die wir herausgezogen oder entfernt oder ersetzt . Es ist also ein Ziff. Wir haben eine Funktion wie count verwendet, wenn Text gleich Platz ist. Aber leider gibt es das in Excel nicht, weshalb wir diesen Ansatz verfolgen. Also lassen Sie uns voran und nehmen tatsächlich diesen Unterschied in der Formel wieder. Ich werde direkt nach dem Gleichheitszeichen ändern, wir nehmen die Länge unserer ursprünglichen Zeichenfolge de Tu minus unserer neuen Länge dieser ersetzten Version der Zeichenfolge, und das wird uns die Zahl acht geben. Ziehen wir das hier wieder rein. Also haben wir acht Leerzeichen entfernt und wir können überprüfen, weil dies eine ziemlich kurze Beschreibung ist. 12345678 Räume wurden tatsächlich entfernt. Aber wir sind nicht ganz da, denn wenn man die Wörter zählt, sind es eigentlich neun Wörter, weil jeder Satz mit einem Wort beginnt und endet. Es wird also immer ein Wort mehr geben als die Anzahl der Leerzeichen. Sehr einfache Einstellung zu machen. Fügen Sie einfach eine zu der Sie nie Formel drücken Sie die Eingabetaste. Und da gehst du. Sie erhalten eine Wortanzahl von 9123456789 Und wir sollten gut gehen, indem Sie einfach doppelklicken es nach unten anwenden. Und da gehst du. Es wird die Wortzahl für jede einzelne dieser Beschreibungen erfordern. Du kannst sie zählen, um zu bestätigen, wenn du willst, aber glaub mir, sie machen den Job erledigt. Nun, das einzige andere Stück, das wir hinzufügen können, wenn wir noch ein Maß an Angus-Sicherheit nehmen wollen das einzige andere Stück, das wir hinzufügen können, wenn wir noch ein Maß an Angus-Sicherheit nehmen wollen,könnten Sie es nennen, um Fälle zu berücksichtigen, in denen,wissen Sie, es könnte eine , könnten Sie es nennen, um Fälle zu berücksichtigen, in denen, wissen Sie, führenden Raum, wissen Sie, zufällig so, was darauf hindeuten würde, dass es 1/10 Wort, wenn sie tatsächlich sind, ist nicht für das zu erklären . Wir werden diese Trimmfunktion benutzen, und alles, was wir tun werden, ist, sie hier rein zu nisten. Also statt der Länge von D zwei, werden wir die Länge der getrimmten Version von D zwei und derselben Logik nehmen, anstatt D zu ersetzen, werden wir die getrimmte Version F D auch hier ersetzen. Also verschachtele ich das nur innerhalb der Funktion hier. Und ich glaube, wir brauchen noch eine Schlussprinzessin. Und lassen Sie uns das einen Schuss geben und drücken Sie die Eingabetaste. Da gehen wir. Also, jetzt ist es auf neun korrigiert worden, was wir hier wollen. Dasselbe, wenn wir nachfolgende Leerzeichen wie diese hinzufügen, ob es ein Whoops oder zwei oder drei oder vier ist, ist es egal. Diese Trimmung wird das für uns berücksichtigen und diese führenden oder nachgestellten Leerzeichen entfernen . So können wir das jetzt anwenden. Und während ein haben wir einen voll funktionsfähigen Wortzähler in Excel. 9. Abhängige Drop-Downs mit INDIRECT: Alles klar. Dieser nächste Profi-Tipp wurde von einer Frage inspiriert, die ich vor einiger Zeit bekam. Habe es seitdem tatsächlich ein paar Mal bekommen. Welches ist? Wie erstelle ich ein Dropdown- oder Datenvalidierungsmenü, das von einem anderen abhängt? Also, anderen Worten, wie erstelle ich eine primäre Dropdown-Liste und dann eine abhängige Dropdown-Liste, in der sich die Liste basierend auf der Benutzerauswahl ändert ? Und es ist eine ausgezeichnete Frage und eine großartige Gelegenheit, eine Funktion namens Indirekt zu verwenden. Dies ist also ein Vier-Sterne-Tipp, definitiv ein wenig auf das fortgeschrittene Ende des Spektrums zukommt. Aber lass mich dich durch, wie das funktionieren wird. Im Wesentlichen können wir indirekt in Kombination mit Datenvalidierungsregeln verwenden, um diese Dropdown-Listen zu erstellen , die auf einer Benutzerauswahl aktualisiert werden. Also werden wir hier in unserer Demo drei Schritte durchlaufen. Wir werden damit beginnen, einzelne Listen zu erstellen, die alle möglichen Auswahlmöglichkeiten enthalten . Also haben wir unsere primäre Drop-down, die in diesem Fall ist die Saison entweder Sommer oder Winter. Und dann haben wir die Listen, die potenziell die zweite oder abhängige Drop-down-Liste füllen könnten , die die Liste der sommerspezifischen Sportarten und winterspezifischen Sportarten ist. Sobald Sie also diese Listen mit eindeutigen Werten erstellt haben, die diese möglichen Auswahlen erfassen , besteht Schritt 2 darin, diese in tatsächlich benannte Bereiche umzuwandeln. Sie können den Namensmanager auf der Registerkarte Formeln verwenden, oder Sie können den Namen einfach direkt in das Namensfeld links neben der Bearbeitungsleiste eingeben. Also, wenn Sie sich die beiden Optionen in der primären Drop-Down-Sommer oder Winter ansehen, werden Sie wollen, um diese Formulierung genau zu entsprechen. Statt Sommersport oder Wintersport verwenden wir also genau diese Worte Sommer und Winter. Und dann von da aus werden wir nur einen Tropfen machen. Downs sind primär und sekundär und konfigurieren dann die Listenquelle für die sekundäre. Legen Sie die abhängigen. Dropdown, um diese erste Zelle als Teil einer indirekten Funktion zu referenzieren. Ich werde darüber reden, warum und wie wir das machen, wenn wir in die Demo kommen, aber das ist die kurze Zusammenfassung des Ansatzes, den wir verfolgen werden. So einige häufige Anwendungsfälle hier erstellen dynamische Berichte mit mehreren Benutzereingaben mit Datenvalidierung oder vielleicht häufiger, konfigurieren Sie Ihre Dropdown-Listen, um zu verhindern, dass Benutzer ungültige Kombinationen von Optionen auswählen . In dieser Demo machen wir genau das, was wir tun. Wir möchten nicht, dass der Benutzer in der Lage ist, die Sommersaison und Eishockey oder Alpinski als Sport zu wählen , denn was auch immer Zellen sind abhängig von dieser Datenvalidierung Auswahl, sie werden offensichtlich nur als leer in diesen Fällen erscheinen. Genug genug Theorie reden. Lass uns unsere Ärmel hochrollen, Kopf zu ihrem Profi Tipps Arbeitsmappe und bauen einige abhängige Drop-Downs. In Ordnung, also von Ihrem Inhaltsverzeichnis Tab, werden Sie nach der abhängigen Drop-Downs Demo in unserer grünen Formel Abschnitt vier Sternen-Tipp suchen . Gehen Sie voran und klicken Sie auf Link Springen Sie in die Stadt und was Sie hier sehen werden, ist, dass wir einige Olympische Athleten Daten nach Jahreszeiten und nach Sport und Collins aufgeschlüsselt und mit den Athleten Namen und kommen sehen, und ich habe nur eine Art leere Vorlage oder Platzhalter hinzugefügt wo wir unsere Informationen füllen, um diese zu erstellen. Zum Drop-down verkauft die Saison, die unsere primäre Drop-down ist, und der Sport, der abhängig ist Drop-down. Mit anderen Worten, wir wollen, dass diese Sportart fallen gelassen wird, um eine Liste von Sommer- oder Wintersportarten anzuzeigen, je nachdem, was hier ausgewählt wurde und F drei verkaufen und dann habe ich hier eine Formel hinzugefügt . Anzahl der Athleten. Dies ist nur Konto ifs Funktion, die besagt, Okay, lassen Sie uns die Zeilen zählen diese beiden Kriterien gegeben, so dass wir die Anzahl der Athleten für eine bestimmte Sportart und Saison sehen können , so dass wir loslegen Schritt eins ist, einfach alle unsere Listen, richtig? Wir beginnen mit der Saison für einen primären Drop-down nur zwei Werte hier. Es ist eine einfache ist, dass Sommer Winter. Geben Sie sie einfach manuell ein. Es ist wahrscheinlich der schnellste Weg und wie man die einzigartigen Sommersportarten und Wintersportarten wenige Möglichkeiten, es zu tun. Wir könnten die erweiterte Filteroption verwenden, die einen weiteren Profi-Tipp wiederhergestellt hat. Oder in diesem Fall werde ich meine Quelldaten hier filtern. Schauen wir uns nur die Sommerminute an. Greifen Sie Selby auf, und steuern Sie die Umschalttaste nach unten, um die gesamte Wertespalte zu wählen. Siehe zu kopieren, gehen Sie zurück, wählen Sie I zu und steuern V zu Einfügen und dann in meiner Datenstadt, Duplikate zu entfernen und nur mit dieser aktuellen Auswahl und Ruhe fortgesetzt. Ich drücke „Entfernen“. Ok. Und da gehst du. Wir haben hier 36 einzigartige Sommersportarten aufgelistet, und wir werden den gleichen Prozess für den Wintersport verfolgen. Also, anstatt auf Sommerfilter im Winter zu filtern, greifen Sie die erste Wertsteuerungsverschiebung. Pfeil nach unten Steuerung. Siehe zum Kopieren. Ich werde nach oben scrollen, und wir müssen das ungefiltert, damit wir wieder Zugang zu Rudern haben. Löschen Sie den Filter. Wählen Sie J, um V zu steuern, um Daten entfernt Duplikate einfügen. Denken Sie daran, nur mit dieser aktuellen Auswahl fortzufahren und drücken Sie, um zu entfernen. So gab es im Begriff, 15 einzigartige Wintersportarten zu bekommen. Also da hast du es. Schritt eins ist abgeschlossen. Habe unsere einzigartige Liste von Optionen. Nun, Schritt zwei besteht darin, diesen Listen tatsächlich aussagekräftige Namen zu geben. Also, was wir tun werden, ist, alle unsere Sommersportarten zuerst den ganzen Weg durch das Wrestling auszuwählen . Der einfachste Weg, dies zu tun, ist nicht durch Formeln definierten Namen gehen, sondern einfach den Namen hier in das Namensfeld links von der Bearbeitungsleiste eingeben. Und denken Sie daran, ich möchte es genau im Einklang mit meinem primären Drop-down nennen, so Sommer geben Sie dasselbe mit Winter. Wählen Sie diese Sportarten in diesem einen Winter eingeben und das ist Schritt zwei. Letzter Schritt ist, unsere Drop-Downs hier in F drei und F fünf einzurichten. So sind unsere 1. 1 erfahrene Auswahl. Sehr, sehr einfach. Wir gehen zur Datenvalidierung. Wir erlauben hier eine Liste und diese Liste. Enthält sind zwei Jahreszeiten Presse. OK, so jetzt können Sie wählen Sommer unseren Winter. Da gehst du. Hier ist der Schlüssel. Es hängt alles davon ab, richtig, richtig, denn diese Sportauswahl und F fünf, wenn wir zur Datenvalidierung gehen, werden wir eine Liste zulassen, wie wir es normalerweise tun würden. Aber anstatt einen Bereich auszuwählen, der eine feste Referenz erstellen würde, um anzuordnen, würde sich das nicht ändern, wäre nicht dynamisch. Hier müssen wir diese indirekte Funktion verwenden. Und was wir versuchen zu tun, ist, dass wir Zelle F 3 indirekten und referenzieren, bevor ich anschließe . Okay, was wir hier tun, ist, dass wir Excel erzählen. Hey, sieh dir Zelle F drei an. Sie werden ein Wort entweder Sommer oder Winter durch indirekte, was wir sagen XLs , dass es mehr zu sehen Es ist nicht nur eine Textzeichenfolge, die das Wort Sommer oder Winter hat. Diese Worte sind aussagekräftiger. Ihre tatsächlich definierten Arbeitsmappenobjekte, also durch die Verwendung von indirekten, sagten Excel Toe tatsächlich diese als benannte Bereiche zu behandeln, die sie sind. Und wenn wir OK drücken, wurde Sommer ausgewählt? Werfen Sie einen Blick, dass Drop-Down all unsere Sommersportarten und keiner unserer Wintersportarten enthält , richtig? Und wenn wir das jetzt in Winter ändern, werfen Sie einen Blick. Alpin Ski Biathlon Bobsport Dies sind unsere Wintersportarten bis hin zum Eisschnelllauf , so dass indirekte Funktion in der Lage ist, dieses Wort als unbenannten Bereich zu erkennen und die Tarifliste entsprechend zu füllen . Also werfen Sie einen Blick. Wir haben unseren kleinen Athleten Zähler, der uns sagt, dass es 645 Alpinskifahrer bei den Winterspielen gab . Wir können es auf den Sommer umstellen und einen unserer Sommersportarten wie Baseball 191 Athleten auswählen . Also da hast du es. Dies ist einer meiner Lieblingstipps im Kurs, weil es nicht so schwer ist, aber es ist extrem leistungsfähig und eine wirklich gute Option, wenn Sie dynamische Berichte und Dashboards erstellen . Also, da Sie gehen, Erstellen einer abhängigen Datenvalidierung, Dropdowns mit der indirekten Funktion 10. Hyperlinks zwischen Arbeitsblättern: Alles in Ordnung für diesen Profi-Tipp. Ich möchte Ihnen zeigen, wie Sie dynamische Arbeitsmappenverknüpfungen mithilfe der Hyperlink-Funktion erstellen. Was wir hier tun werden, ist, dass wir Hyperlink verwenden, um Benutzer direkt mit bestimmten Arbeitsmappen oder Arbeitsblättern zu verbinden . nun dem Kurs folgen, Wenn Sienun dem Kurs folgen,sollte dies sehr, sehr vertraut klingen, denn genau das habe ich in der Registerkarte Inhaltsverzeichnis in der Protip-Arbeitsmappegemacht Protip-Arbeitsmappe Sie sehen eine Formel wie Dies, das von dieser Hyperlink-Funktion gesteuert wird, und das wird Sie direkt darauf zeigen, eine für einen bestimmten Tab zu verkaufen. In diesem Fall gehen sie zur Registerkarte Optionen. In der Theorieist das ziemlich unkompliziert, aber Sie werden feststellen,dass es ein Vierstern-Tipp ist. In der Theorie ist das ziemlich unkompliziert, aber Sie werden feststellen, Also ist es am fortgeschrittenen Ende der Skala. Und der einzige Grund, warum ich es als fortgeschrittenen Tipp bezeichnet habe, ist, weil die Syntax extrem spezifisch und ziemlich unflexibel ist . Also habe ich hier ein Beispiel aufgenommen. Hyperlink Blatt eine Zelle und eine Zelle mit einer Linknamenbeschriftung und beachten Sie alle diese Sonderzeichen dort. Die Anführungszeichen einfache Anführungszeichen, das Hash-Zeichen, das Ausrufezeichen. Alle diese Symbole sind kritisch. Und wenn Sie nicht alle genau an der richtigen Stelle haben. Diese Funktion funktioniert nicht. Also wirklich mehr als alles andere, Hyperlink zur Arbeit zu bringen, ist mehr eine Frage der Liebe zum Detail als alles andere auf eine Sache zu beachten. Hier können Sie eine tatsächliche Blattreferenz eingeben, wie das Beispiel in diesem Aufzählungszeichen. Oder Sie können dies durch einen Zellbezug ersetzen, wie ich es im Inhaltsverzeichnis getan habe . Wenn Sie das tun, macht es die Dinge ein wenig komplizierter, weil Sie im Wesentlichen die genaue Syntax mit Can Canton acht Funktionen rekonstruieren müssen , die tatsächlich dieses Hash-Ausrufezeichen und Anführungszeichen einschließen . Die Anwendungsfälle hier, zum einen, fügen Sie Tools hinzu, um Benutzern zu helfen, größere, komplexe Arbeitsmappen zu navigieren , wie zum Beispiel eine Pro-Tip-Arbeitsmappe, die etwa 100 Registerkarten mit Informationen enthält. Ähm, und zwei. Dies ist eine, die tatsächlich ziemlich viel tun, Erstellen von Berichten, die Links zu zusätzlichen Details oder Datenquellen haben. Vielleicht haben Sie einen Bericht für einen Klienten zur Verfügung gestellt. Das ist die Übersichtsansicht der oberen Zeilen. Sie können einen Link hinzufügen, der möglicherweise zu einem viel detaillierteren Blatt gesteuert werden kann. Wenn der Benutzer auf diese Detaillierungsstufe zugreifen möchte Wirklich hilfreiches Tool in diesen Arten von Fällen. Lassen Sie uns also voran und erstellen Sie unseren eigenen Hyperlink in der Pro-Tip-Arbeitsmappe. Ordnung, also, angemessen genug, hier sind wir in unserem Inhaltsverzeichnis Blatt, das zufällig Dutzende und Dutzende von Beispielen dieser Hyperlink-Demo enthält, die wir im Begriff sind zu üben. In diesem Fall wollen wir tatsächlich auf die dynamische Arbeitsmappe Links Demo direkt hier in unseren Formel-Tipps gehen. Und du hast es erraten. Wir werden direkt auf diese Registerkarte verlinken. Und was wir hier haben, ist wie eine kleine Mini-Version dieses Inhaltsverzeichnisses, das wir selbst ausbauen werden. Die Idee wird also sein, Spalte H mit Links zu füllen, die auf diesen Arbeitsmappennamen oder Arbeitsblattnamen und Spalte B basieren . Aber bevor wir versuchen, alle verrückt und fortgeschritten direkt von Schritt eins zu werden, wollen wir versuchen, einfach eine einfache Art mehr zu schreiben grundlegende Hyperlink-Funktion für eine in der Nähe befindliche Registerkarte. Das Zählen von Wörtern ist in der Nähe. Lasst uns zuerst damit arbeiten, damit wir es versuchen können. Nur Eingabe gleich Hyperlink-Position wird Wörter zählen. Verkaufen Sie ein Komma über zum nächsten Argument, das der freundliche Name wie Link ist. Schließen Sie Anführungszeichen, schließen Sie die Klammer drücken Sie die Eingabetaste. Dinge sehen gut aus, oder? Wenn wir nur diese Zelle auswählen, verlinken wir mit dem Zählwortblatt. Konkret eine Zelle und wir haben ihn Link beschriftet, und es hat einen Link für uns erstellt. Das Problem ist, wenn wir darauf klicken, erhalten wir, dass dies den angegebenen Dateifehler nicht öffnen kann. Und im Grunde, was wir Excel sagen müssen, ist Hey, Excel, wir versuchen nicht, eine separate Datei zu öffnen. Wir versuchen, nur auf eines unserer Blätter oder Registerkarten in dieser Datei zuzugreifen. Also, um Excel zu sagen, das ist, was wir wollen. Wir müssen hier ein paar Zeichen hinzufügen, und gleich nachdem Perrin geöffnet wurde, fügen wir ein doppeltes Anführungszeichen gefolgt von einem Hash-Zeichen hinzu. Und dann am Ende, gleich nach dem Zellbezug, werden wir ein weiteres doppeltes Zitat schließen und dann die Eingabetaste drücken und das überprüfen. nun Wenn wirnunauf Link-Boom klicken, ein einzählendes Wortblatt, hat es perfekt funktioniert. Wenn Sie also dieses Anführungszeichen hinzufügen, um die Blattreferenz zu umgeben, sowie dieses Hash-Zeichen hat Excel gesagt, dass wir tatsächlich versuchen, zu einem Arbeitsblattspeicherort in dieser Datei zu navigieren. Also lassen Sie uns zurück zu unserem dynamischen Arbeitsmappenverknüpfungsblatt springen, und wir könnten den gleichen Ansatz verfolgen und es tun, wissen Sie, 15 weitere Male, um den Rest dieser Links zu füllen. Aber offensichtlich ist das ein bisschen langsam. Es ist ein wenig manuell, und wenn Sie mehrere 100 Links füllen müssen, ist das wirklich keine machbare Option. Und da wir unsere Arbeitsblattnamen hier in Spalte B haben, sollten wir in der Lage sein, diese Namen einfach als Teil unserer Hyperlink-Funktion zu referenzieren. Aber hier ist der Haken. Wir haben einfach versucht, diese Blattreferenz hier durch einen Zellbezug wie B 11 beeindruckt zu ersetzen . Okay, wir würden nur direkt mit dieser Zellenposition innerhalb des aktuellen Blattes verknüpfen, es nicht als Name eines Tabs zu lesen . Also werden wir tun müssen, ist ein bisschen clever zu werden, ein wenig schlauer mit Can Canton acht Funktionen, um die Syntax zu erzwingen, die wir brauchen. Also achten Sie auf Ihre von Folk ALS gelegt. Lassen Sie uns hier schön und vergrößert werden, weil wir mit dieser Syntax ziemlich detailliert werden und löschen, was wir hatten, und wir werden mit einer sauberen Leinwand offene Klammer beginnen Folgen Sie mir genau hier. Doppeltes Anführungszeichen, Hash-Zeichen. Einzelnes Anführungszeichen. So sieht das so weit bekannt aus. Hier ist, wo es ein bisschen seltsam wird. Wir fügen noch ein doppeltes Anführungszeichen ein kaufmännisches Und-Zeichen hinzu. Kaufmännisches Und-Zeichen in einer Referenz, die Zelle, die wir wollen 11 und eine andere sein und Prozent schließen das doppelte Anführungszeichen an einem anderen einfachen Anführungszeichen ein Ausrufezeichen, und dann verkaufen sie Referenz, die wir wollen. Also, wenn wir Leute von ihrer Zelle ablegen wollen, eine, die Sinn macht, geben Sie hier ein und schließen Sie dann ein weiteres doppeltes Anführungszeichen Komma bei unserem freundlichen Namen oder Label wie Link. in Anführungszeichen die Klammer, Schließen Siein Anführungszeichen die Klammer,und drücken Sie die Eingabetaste. Jetzt werde ich den Mauszeiger nochmal darüber bewegen, damit du deinen Bildschirm pausieren kannst. Stellen Sie sicher, dass Sie alle diese einzelnen Zeichen an Ort und Stelle geschnitten haben. Hier ist die Sache. Ich würde Leerzeichen hinzufügen, um es ein wenig lesbarer zu machen, aber selbst das würde die Funktion so sehr, sehr zerbrechliche Syntax brechen . Und deshalb ist das ein Vierstern-Tipp. Aber jetzt lassen Sie uns das eine Chance geben. Rechtsklick Link-Boom-Zelle. Eine im Reiter „Zählwörter“ ist jetzt der Moment der Wahrheit, richtig? Gehen wir zurück und greifen Sie einfach diese und ziehen Sie es auf ein paar mehr. So abhängig. Drop-Downs. Klicken Sie auf den Link. Da sind wir abhängig. Drop-Down-Zelle einer dynamischen Arbeitsmappe Verknüpfungen. Das ist die Zelle, in der wir gerade sind. Boom Zelle A ein Index und passen Sie eine perfekt funktioniert. Jetzt haben wir im Wesentlichen nur den Prozess automatisiert, um alle diese Links einzeln erstellen zu müssen . Und jetzt, wie jede andere Formel, können Sie einfach klicken und ziehen und sie an jedem anderen Ort anwenden. Lassen Sie die Verweise auf Laufwerkbenutzer basierend auf den Arbeitsmappenblättern und Spalte B ändern, so dass es praktisch gut ist, die Hyperlink-Funktion zu verwenden, um Benutzer zu bestimmten Arbeitsblattspeicherorten in Ihrer Excel -Datei zu bewegen. 11. „Fuzzy Match“ Lookups: Ich möchte ein paar Minuten dauern und über etwas sprechen, das ich Fuzzy Match v Lookups nenne. Und speziell werde ich beschreiben, wie man Wertebereiche findet, im Gegensatz zu genauen Nachschlagewerten mit diesen Arten von Lookups. Jetzt kann Fuzzy-Übereinstimmung, die formell als ungefähre Übereinstimmung bekannt ist, verwendet werden, um zu bestimmen, wo ein bestimmter Nachschlagewert in einen bestimmten Satz von Bereichen fällt. Die Idee hier, der entscheidende Punkt ist, dass anstatt zu versuchen, die genaue Übereinstimmung für unseren Nachschlagewert zu finden, was wir in diesem Fall tun, nach der nächsten Übereinstimmung zu suchen, die entweder gleich oder kleiner als der Nachschlagewert ist . Und um dies zu erreichen, alles, was wir tun, ist, das letzte Argument und die Nachschlagefunktion zu ändern. Beachten Sie, dass der Bereich nachschlagen wird, indem Sie den Wert true oder die Nummer eins eingeben, was eine Fuzzy-Übereinstimmung oder eine ungefähre Übereinstimmung bedeutet. Nun, wie die meisten Dinge in Excel, wird dies am besten mit einem Rheal-Kontextbeispiel veranschaulicht. Werfen Sie einen Blick auf diesen Datensatz hier. Wir haben eine Reihe von Produkten, die in Spalte B verkauft werden. Wir haben eine Verkaufsmenge oder ein Auftragsvolumen und Spalte C. Aber der Verkaufspreis und D Die Idee ist, wir versuchen, die Rabattstufe in Spalte e basierend auf den Verkäufen zu berechnen, Menge oder Auftragsvolumen. Und im Wesentlichen ist hier die Idee, dass größere Mengenaufträge einen größeren Rabatt erhalten. Für Bestellungen von 0 bis 5 wird also kein Rabatt angewendet. 6 bis 10 wäre ein 5% Rabatt, bis zu Bestellungen von mehr als 50 Einheiten, die einen Rabatt von 20% verdienen würden. Damit diese Logik tatsächlich funktioniert, werden wir eine V Nachschlagefunktion verwenden, und wir werden sagen, Lassen Sie uns den Wert in der Spalte nachschlagen. Sehen Sie die Menge. Wir werden es in diesem Bestell-Rabatt-Tisch nachschlagen. Speziell Spalten H bis. Ich Wir wollen die zweite Spalte über und hier ist der Schlüssel. Wir verwenden den True Range Nachschlagetyp , der ungefähre Übereinstimmung ist. Und wenn wir diese Formel eingeben, passiert jetzt, dass X Ellis wieder von oben in Spalte H nach unten fährt und nicht nur nach der exakten Übereinstimmung in diesem Fall 25 sucht, sondern die engste Übereinstimmung, die entweder gleich 25 oder weniger als 25. Also in diesem Fall stoppt es bei 21. Der Spaltenindex sagt uns, über die nächste Spalte in der Tabelle Array zu bewegen, und dann, dass 15% Rabatt. Das ist der Wert, der in unsere Rabattspalte hochgezogen wird. E. Was wir also im Wesentlichen getan haben, ist nicht genau mit dem Mengenwert übereinstimmen, sondern zu identifizieren, in welchen Wertebereich diese Menge in unserem Tabellenarray oder einer Nachschlagetabelle fällt . So wirklich hilfreicher Tipp in der Gesäßtasche zu haben. Eine Sache zu beachten, dass das hier ziemlich kritisch ist. Damit dies ordnungsgemäß funktioniert, müssen Ihre Gruppierungen durch den Mindestwert definiert werden, d. h. die Mindestanzahl der Reihenfolge, die Sie sehen, dass sie sich in Spalte H in der Nachschlagetabelle und in diesen Mindestanforderungen befinden. Diese Mindestwerte müssen aufsteigend von klein nach groß angeordnet werden, da denken Sie daran, wie ein V-Nachschlagen funktioniert. Es wird an der Spitze beginnen. Es wird Zelle für Zelle gehen, bis es die nächste Übereinstimmung findet. Also, Ordnung ist hier kritisch. Jetzt ist das ein Vierstern-Tipp. Es ist relativ weit fortgeschritten. Wenn Sie in der traditionellen Ansicht keinen guten Griff haben, schlagen Sie Funktionen nach, wie dies ein wenig verwirrend sein kann. Ich bedecke V-Lookups sowie H Lookups Index Match-Offset usw. in meinen Formeln und Funktionen. Natürlich werden wir mehr der Grundlagen ausführlich behandeln, so dass es Sie auf jeden Fall ermutigen, das zu überprüfen. Jetzt ein paar Anwendungsfälle hier können wir so etwas wie eine variable Provisionsrate basierend auf einem Agentenverkaufsvolumen definieren . Oder wir können so etwas tun, wie wir hier zeigen die Berechnung von Tränenrabatten basierend auf so etwas wie einer Kaufmenge. Also lassen Sie uns in die Pro-Tip-Arbeitsmappe springen und üben, eine dieser Fuzzy-Match oder ungefähre Ansicht zu schreiben . Nachschlagen Funktionen Alles klar, Also aus unserem Inhaltsverzeichnis waren in der Formel-Tipps Abschnitt. Suchen Sie nach der Fuzzy Match Lookups Demo und gehen Sie voran und drücken Sie Link, um zu diesem grünen Tab zu gelangen . Und was wir hier haben, bestellen im Grunde Datensätze mit unterschiedlichen Bestellideen in jeder Zeile, und wir schauen uns an, welches Produkt verkauft wurde. Menge verkauft, Verkaufspreis, die wir in diesem Beispiel nicht verwenden werden, Und der Schlüssel ist Spalte E.Diese Und der Schlüssel ist Spalte E. Rabattspalte , die wir mit Avi Nachschlagefunktion füllen müssen, die auf diese Bestellung verweist und Rabatt-Tabelle hier. Jetzt. Eine Sache zu beachten, diese Spalte h ist die, die uns wirklich wichtig ist, und das ist diejenige, die die Eingabe für die Funktion bedienen wird. Beachten Sie, dass es basierend auf dem Minimalwert in jeder Gruppe oder Bucket definiert ist, und es wird aufsteigend diese Beschriftungen hier und Spalte G sortiert. Diese werden nicht tatsächlich in der Funktion verwendet. Ich habe sie gerade für ein wenig Lesbarkeit hinzugefügt, also springen, um es zu verkaufen, um darüber nachzudenken, wie wir dieses Problem richtig angehen könnten? Es gibt ein paar Möglichkeiten, wie wir das in Angriff nehmen könnten. Sie könnten eine Reihe von verschachtelten if-Anweisungen verwenden und sagen, Sie wissen, wenn die Menge kleiner als X und größer ist als warum, dann beträgt der Rabatt 10%. Das ist völlig in Ordnung. In diesem Fall würden Sie verschachteln. Du weißt schon, fünf verschiedene Bedingungen in der gleichen Formel. Es wird ein bisschen lang, ein bisschen chaotisch, und Sie müssten jede dieser Bedingungen jederzeit ändern, wenn Sie die Dinge ändern wollten. Das ist also nicht wirklich die effizienteste Option hier. Was ist, wenn wir eine genaue Übereinstimmung verwenden, um die richtige Art des Standard-Ansatzes zu suchen. Wir sagten, Lassen Sie uns Quantität und Sitz nachschlagen Lassen Sie uns versuchen, diese Menge zu finden. Sie befinden sich in unserem Tabellenfeld jeweils drei mal sechs, drücken Sie vier, um das zu sperren. Und was wir wollen, ist der Rabatt , der in der zweiten Spalte dieses Tabellenarrays lebt. Und dann, wenn wir unsere Standardart falsch oder Null machen, um die genaue Übereinstimmung zu bedeuten und sie abzuschließen, könnten wir drücken, eingeben und beginnen, dies irgendwie anzuwenden. Und was Sie sehen werden, ist, dass einige Felder zufällig richtig ausgefüllt werden. Aber diese Luft auf Lee diejenigen, die genau gleich dem Order Minimum 21 6 waren, zum Beispiel, um diese Logik von einer genauen Übereinstimmung zu einer engen Übereinstimmung zu ändern und gleich oder weniger als alle, die wir tun müssen. Jetzt wurde diese Null in eine Eins oder eine echte drücken Enter geändert, und jetzt ist es eine ungefähre oder unscharfe Übereinstimmung geworden. Suchen Sie nach, das richtig angewendet wird, wenn es auf alle diese Zeilen Es ist also ein Doppelklick, um es ganz nach unten anzuwenden. Sieht so aus, als hätten wir hier etwa 100 Bestellproben und das scheint gut zu funktionieren. Spot-Check schwächen. Fünf wäre 0 25 wäre 15%. Eine Bestellmenge von 75 wäre 20. Sieht toll aus. Also da hast du es. Das ist ein großartiges Beispiel für einen praktischen Anwendungsfall für ungefähre Match- oder Fuzzy-Match-V-Nachschlagefunktionen . 12. Random Seltenheit mit OFFSET & RANDBETWEEN: Na gut, Leute, ich bin so aufgeregt für diesen nächsten Tipp. Es ist einer meiner Favoriten aller Zeiten. Ich weiß, dass ich das viel sage, aber diesmal meine ich es ernst. Es ist definitiv die Top zwei oder drei. Zumindest werden wir darüber sprechen, wie man Elemente aus einer Liste mit Offset randomisiert und zwischen Funktionen lief . Im Wesentlichen, was wir hier tun werden, ist, dass wir Offset mit einem ganzen Zahlenzufallszahlengenerator kombinieren werden , der Rand zwischen verwendet, um im Wesentlichen um zwei zufällige Rose innerhalb einer Liste oder einem Zellbereich zu springen . Nun, Sie fragen sich vielleicht, warum sollte ich das jemals tun wollen? Was ist der Sinn? Und ich kann Ihnen versichern, dass es sehr ernste, sehr anwendbare Gründe gibt , diese Tools zu lernen, wie zum Beispiel den Aufbau eines eigenen Bandnamen-Generators. Hier haben wir zwei Listen von Wörtern. Wir haben urkomische Adjektive in Spalte A und wir haben jetzt urkomisch Besitzer und Spalte B, und unsere Mission hier ist es, diese Offset zu verwenden und lief zwischen Funktionen zufällig den Namen unserer nächsten Band zu generieren . Nun ist die Schönheit dieser ist, dass, weil lief zwischen ist eine flüchtige Funktion. Es bedeutet, dass unsere Bandnamen mit jeder Änderung der Arbeitsmappe regenerieren oder wenn wir diese F 9 berechnen Verknüpfung treffen . Also habe ich schon eine peinliche Menge an Zeit verschwendet. Ich erfrische das, weißt du, spät an einem Freitagabend, und lachte vor mir selbst. Aber ich würde gerne ein paar Bandnamen hören, die ihr euch einfällt. Ich glaube, meine Top-Typen sind jetzt Ah, kriegerische Schneemann. Das war ziemlich gut in skurrilen Kätzchen war ein nettes. Und, natürlich, die weißen heißen Sonnenblumen. Ich würde sagen, die dort in meinen Top 3, also sag mir Bescheid, wenn du die oben kannst. Hinterlassen Sie einen Kommentar. Ich liebe es, ihn zu hören. Nun natürlich gibt es natürlichandere gängige Anwendungsfälle, wie das Erstellen von Riel-Geschäftsmodellen oder Szenarien, in denen Sie bestimmte Eingaben randomisieren möchten . Oder, natürlich, bauen Sie Ihre eigene urkomische Band namens Generator. Denn warum nicht so mit dem genug reden darüber, Lassen Sie uns in Excel springen und tatsächlich bauen dieses Tool. Alles klar, sobald Sie Ihre Pro-Tipps Arbeitsmappe geöffnet haben, werden wir nach den ausgewählten zufälligen Listenelementen suchen. Demo. Es ist eine grüne Registerkarte in unserem Formeltipps Abschnitt, oder Sie können zu Ihrem Inhaltsverzeichnis gehen und direkt darauf verlinken. Und hier haben wir unseren Bandnamengenerator, der Listen mit jeweils 30 Artikeln enthält, und alles, was wir tun müssen, ist, diesen zufälligen Gegenstand aus der Liste einen zufälligen Gegenstand aus Liste zwei zu ziehen und Canton acht zu unserem Bandnamen zu kombinieren. Jetzt gibt es tatsächlich ein paar Möglichkeiten, dies zu tun. Könnte Indexfunktionen verwenden, die Sie beide perfekt gültigen Werkzeuge zum Abrufen von Werten von einer bestimmten Position innerhalb eines Bereichs verwenden könnten. In diesem Fall werden wir Offset benutzen. Lassen Sie uns voran gehen und beginnen Sie mit der Eingabe dieser Funktion. Wir werden von einem bestimmten Verweis oder Startpunkt in diesem Fall sind Header von Liste A Zelle versetzt? Eine Eins. Lassen Sie uns es mit F vier jetzt die Anzahl der Zeilen sperren, die wir diese Referenz nach unten verschieben möchten . Hier kommt die Zufallsbestimmung ins Spiel. Wir wollen nicht immer eine, zwei oder zehn Reihen nach unten gehen. Wir wollen nach unten eine Zufallszahl zwischen einem bestimmten Minimum und Maximum als die unteren oder oberen Werte bekannt zu bewegen. Die minimale Anzahl von Zeilen, die wir verschieben möchten, ist eins, denn das würde uns eine Straße hinunter in den ersten Punkt unserer Liste Aquatic und die größte Zahl, die wir zurückgeben wollen, ist 30, was uns den ganzen Weg nehmen würde am Ende unserer Liste. Jetzt ist das alles, was wir für diese Iteration der Funktion tun müssen. Wir können voran gehen und schließen Sie die Klammer Komma über das nächste Argument, das Spalten ist. Und weil wir nicht von unserem Ausgangspunkt nach links oder rechts bewegen wollen, werden wir hier eine Null benutzen. Und weil wir keinen Bereich oder ein Array mit einer bestimmten Höhe oder Breite zurückgeben möchten, können wir die Funktion hier abschließen und die Eingabetaste drücken. Also ist es in diesem Fall einen zufälligen Gegenstand gezogen, elegant aus der Liste eins. Und anstatt das Rad neu zu erfinden, lasst uns voran gehen und einfach diese Formel kopieren. Fügen Sie es auch nach innen ein. Und alles, was wir tun müssen, ist, diesen Ausgangspunkt von Zelle eins zu ändern, um eins zu sein, und wir sollten gut gehen. So hässlich und Kunstwerke sind die beiden Wörter, die zufällig zurückgegeben wurden. Letzter Schritt hier ist nur, um sie zu kontaminieren. Kombinieren Sie sie zusammen zu diesem Bandnamen, also sagen wir, gleich dem Leerzeichen in Anführungszeichen kaufmännisches Und-Zeichen für concoct Nate. Schnappen Sie sich das erste Wort und Prozent offene Zitat an einem Leerzeichen und dann schließlich greifen, dass das zweite Wort drücken Sie die Eingabetaste und da gehen wir. The Magnificent Bubbles ist unser erster randomisierter Bandname. Und denken Sie daran, wegen der Tatsache, dass zwischen seinem flüchtigen lief, können Sie entweder die F neun Verknüpfung drücken oder gehen zu Formeln. Berechnen Sie jetzt, um zu sehen, dass das immer und immer wieder neu berechnet wird, damit wir es einfach einen Tag nennen wenn unsere Listen zu keinem Zeitpunkt wachsen oder schrumpfen werden. Aber wenn wir ein wenig flexibler werden wollen, können wir tatsächlich diese hartcodierte, maximale Zufallszahl von 30 ersetzen und eine Funktion wie Graf A verwenden, um diese Referenz dynamischer zu machen . Und was das tun wird, ist, dass es die Anzahl der Elemente in dieser Liste zählt, ob es 30 oder 40 oder 50 oder 10 ist und das als maximalen Bereich für unsere lief zwischen Funktion verwendet . Also wieder, es gibt uns ein wenig mehr Flexibilität, so dass wir zu diesen Listen hinzufügen oder daraus entfernen können . Also werde ich die 30-Typzahl A löschen und die gesamte Spalte referenzieren. Ein Sperren mit F vier und nur Fang hier ist, weil wir einen Header haben, der auch nicht leer ist . Wir schließen diese Klammer einfach ab. Subtrahieren Sie eins davon und wir sollten gut sein, unterdrückt zu werden. Geben Sie dort ein. Wir können voran gehen und kopieren Sie die gleiche Formel, fügen Sie sie und wo Sie die Referenzen ändern, um eins zu sein und die Anzahl der Spalte B drücken Sie die Eingabetaste , und wir sind gut zu gehen. Es gibt also unseren praktischen kleinen randomisierten Bandnamen-Generator, den wir hier in Excel gebaut haben . 13. Kombinieren von INDEX & MATCH: Alles in Ordnung für diesen Pro-Tipp geht darüber zu sprechen, wie man Index- und Match-Funktionen kombiniert, um als flexibler nachschlagen zu dienen. Nun, das ist ein Vier-Sterne-Formel-Tipp, definitiv ein bisschen fortgeschrittener. Nun, wenn Sie nicht mit den Grundlagen der Nachschlagefunktionen oder Index und Match vertraut sind, denken Sie daran, dass es Ihnen schwer fällt, hier entlang zu folgen, kann es wert sein, zurückzugehen und einige dieser grundlegenden Kurse zu nehmen. Im Wesentlichen, hier ist, was wir tun werden. Wir werden Index und Match kombinieren, um den Zweck eines V Nachschlags zu ersetzen, und das wird mehrere verschiedene Vorteile bieten. Zum einen müssen wir das nicht. Hard Code. Ein Spaltenindex. Mit anderen Worten, Match wird automatisch unsere Spaltenüberschriften identifizieren, die richtige Übereinstimmung in der Nachschlagetabelle finden und Werte aus den richtigen Spalten ziehen. Zweitens sind die Nachschlagung. Werte müssen nicht auf die gleiche Weise in der ersten Spalte des Tabellenarrays leben, wie sie es mit dem V. Suchen nach oben tun . Es kann überall in dem Array leben, auf das wir verweisen. Und drittens, es wird uns mehr Flexibilität für komplexe Situationen wie den Versuch geben, die zweite oder dritte Instanz eines Spiels zu finden , die in einem separaten Protest abdecken wird. Versuchen wir also zu visualisieren, was hier vor sich geht, bevor wir in unsere Demo springen. Betrachtete Datensätze wie dieser bekamen Rohdaten und Spalten A bis C, die den Umsatz nach Speicher für Jahr betrachteten . Und was wir versuchen zu tun, ist die Spalten D, E und F zu füllen , um Stadt, Staat und Land basierend auf einer Nachschlagetabelle wie dieser zu bringen . Habe Informationen zu jedem Geschäft i d. Ein Etikett auf Adresse, ein Stadtgut und ein Land. Jetzt, um zu sehen, wie das alles verdrahtet ist. Lassen Sie uns auf eine bestimmte Zelle wie D vier achten, die den Stadtnamen Bremerton zieht, und Sie werden diese ziemlich komplexe Index- und Matchfunktion direkt dort in der Formelleiste sehen . Aber im Wesentlichen ist hier, was los ist. Wir weisen Excel an, den gesamten Bereich potenzieller Werte aus unserem unten stehenden Tabellenarray zu indizieren und dann Übereinstimmungsfunktionen zu verwenden, um die bestimmte Zeile zu isolieren, die wir wollen und die spezifische Spalte mit einem einzelnen Wert zu landen. Die erste Übereinstimmungsfunktion sucht also nach der Store-I-D-Nummer in diesem Fall, Nummer drei innerhalb der Spalte I aus unserem Tabellenarray. Es findet diesen Speicher in der dritten Zeile unter den Kopfzeilen und sagt Excel. In dieser dritten Reihe will ich leben. Hier werden wir unsere Werte für die Indexfunktion ziehen. Und dann passiert ein sehr ähnlicher Prozess mit der zweiten Match-Funktion, die unseren Spaltenindex bestimmt. Außer in diesem Fall suchen wir nach diesem Städtenamen oder dem Header-Namen in D eins innerhalb Zeile 1 oder der Kopfzeile unseres Tabellenarrays. In diesem Fall finden wir diesen Stadt-Header. Wir passen diesen Header-Namen in der vierten Spalte, und das sagt unsere Indexfunktion. Wir brauchen einen Wert aus Spalte Nummer vier. Zusammen sagen diese beiden Übereinstimmungsfunktionen dem Index an, sich zu dieser dritten Zeile zu bewegen und zu vierten Spalte zu bewegen und das sich schneidende Ergebnis zurückzugeben, was in diesem Fall Bremerton ist. Und das ist der Wert, der wieder in unseren Tisch nach oben gezogen wird und Defour so definitiv knifflig zu verkaufen , um zuerst einen Dreck zu bekommen. Aber eine wirklich leistungsstarke Kombination von Funktionen, um so häufige Anwendungsfälle hier zu verwenden, viele Nachschlagespalten aufzufüllen, ohne die Formeln manuell aktualisieren zu müssen. Und es gibt Spaltenindexnummern und die Arbeit mit komplexeren Szenarien. Wie ich erwähnt habe, zum Beispiel Lookups, die viele zu viele Übereinstimmungen und mehrere Übereinstimmungen in diesem Tabellenarray haben können. Lassen Sie uns also in Excel springen und üben, eine dieser Indexübereinstimmungsfunktionen zu schreiben. Ordnung, also suchen Sie in einer Pro-Tip-Arbeitsmappe nach der Kombination von Index und Match Green Tab und unsere Formel-Tipps können direkt darauf verlinken. Und hier haben wir unsere Umsatzdaten nach Speicher für Jahr und Collins A bis C und diese drei Spalten, die wir mit einer Nachschlagefunktion auf einer Pole Stadt füllen wollen, Bundesstaat und Land für jedes Geschäft I d. Basierend auf dieser Nachschlage-Tabelle hier in Spalten h bis em. Um der Demonstration willen, fangen wir zuerst mit einer einfachen V Nachschlag-Formel an und sehen, wie das geht. Also schauen wir den Laden von B nach, um nur die Spalte zu reparieren, weil dort die Geschichte, die ich d immer leben wird. Wir werden es innerhalb des Tabellen-Arrays nachschlagen und notieren. Ich kann nicht in Spalte H beginnen, weil das nicht mein Nachschlagen einer Startspalte ist. Ich ziehe mich durch M 11, drücken Sie F vier, um die Referenz zu reparieren und für die City Spalte hier. Ich möchte den Wert von der 1. 2. 3. Spalte zurückgeben. Das ist meine Spaltenindexnummer und mein Bereich Nachschlagen stimmt genau überein, weil ich den genauen Speicher I D abgleichen möchte . In dieser Spalte muss ich Acapulco füllen, was gut aussieht, kann es nach unten anwenden, und alles funktioniert wie erwartet. Und jetzt hier ist, wie die meisten Leute damit fortfahren würden. Wir müssen mehr Spalten, Staat und Land bevölkern . Sie könnten diese Formel über ziehen, und weil wir unsere Referenzen richtig festgelegt haben, müssen wir nur den Spaltenindex von der dritten Spalte in die vierte für Staat und von der dritten zum fünften für Land ändern . Und da gehen wir. Wir können diese packen, um runter zu klettern, und wir haben alle diese Zellen in wenigen Minuten richtig bevölkert, mit einer traditionellen Be-Nachschlagefunktion. Und es ist absolut nichts falsch daran, das zu tun. Gillick up ist unglaublich mächtig. Der einzige Fang ist, dass was, wenn wir Ah füllen müssten, 100 Spalten statt nur drei. Jetzt beginnt die plötzliche Aktualisierung all dieser Spaltenindexnummern manuell wie ein viel mühsamer Kopfschmerzen zu fühlen . Jetzt besteht eine Option darin, den Spaltenindex durch eine Übereinstimmung zu ersetzen und V-Lookup mit Match zu kombinieren , was in diesem Fall gut funktionieren würde. Der einzige Nachteil, denken Sie daran, ist, dass wir immer noch darauf verweisen müssten, dass dieser Speicher I d in der ersten Spalte des Tabellenarrays nachschlagen . Also lassen Sie uns voran und löschen Sie diese V Nachschlagefunktionen. Ich möchte Ihnen den Index Match-Ansatz als Alternative zeigen. Also fangen wir mit einem Index an. So fangen wir immer an, weil wir Excel sagen. Wir wollen einen Wert aus diesem gesamten Array greifen, und jetzt können wir auch Spalte H einschließen, und wir werden diese Referenz beheben. Und jetzt müssen die einzigen beiden Argumente, die uns hier wichtig sind, den Index sagen, von welcher Straße wir ziehen und welche Ruhe aus diesem Array ziehen sollen. Und denken Sie daran, aus der Demo, wir werden in jedem dieser Fälle eine Matchfunktion verwenden. Also, um die richtige Zeile zu identifizieren, werden wir sagen, dass wir mit dem Laden übereinstimmen. Ich d hier und abgestimmt werden wird die Spalte zu beheben. Und wo suchen wir den Laden? Während wir nach dem Laden suchen, d ich in derTabelle Array, wo unsere Geschichten Ideen leben, was ruhig ist, ich mit vier. Während wir nach dem Laden suchen, d ich in der Tabelle Array, wo unsere Geschichten Ideen leben, Um dies in zu beheben, verwenden Sie eine Null für die genaue Übereinstimmung und schließen Sie dann die erste Übereinstimmungsfunktion. Das ist unsere Zeilennummer, die wir in unseren Index einspeisen werden. Nur ein anderes Stück, das wir brauchen, ist die Spaltennummer. Wieder. Wir werden hier einen sehr ähnlichen Ansatz verfolgen. Ist das dieses Mal werden wir unseren Header-Namen übereinstimmen, der momentan City ist. Und wir werden dieses Mal nur die Zeile reparieren, weil unsere Header immer in Zeile 1 leben. Und wo wollen wir zu diesem Header passen? Nun, wir suchen es im Hetero von der Kunst zu finden. Schau Array H eins bis M eins zu vier nach oben. Um diese eine Null für die genaue Übereinstimmung zu beheben. Kleidung, die Funktion übereinstimmen, schloss die gesamte Indexfunktion und drücken Sie die Eingabetaste. Also, da gehst du, wir holen wieder Acapulco. Und jetzt ist hier die Schönheit davon. Wenn wir Acapulco nehmen, ziehen Sie es rüber. Bumm. Wir haben Bundesland und Land automatisch aufgefüllt, ohne diese Spaltenindexnummern berühren zu müssen . Und, wie ich erwähnt habe, dass ich d oder das Nachschlagen Wert muss nicht in der ersten Spalte in diesem Fall leben , die in der zweiten Spalte gelebt. Und das bedeutet, dass wir solche Dinge tun können und sagen können, anstatt Land, geben Sie mir das Ladenlabel und gehen Sie los. Es wird die richtigen Etiketten ziehen, auch wenn diese links von der Ladenidee waren, dass wir nach oben suchen, was etwas ist, das es mit der V Nachschlagefunktion unmöglich wäre. So gehen Sie schnell Crash-Kurs in der Kombination von Index und Match, um flexiblere Nachschlagefunktionen zu erstellen . 14. Passende Elemente zwischen Listen: Na gut, als Nächstes habe ich einen Profi-Tipp für euch. Es ist ein Fünf-Sterne-Formel-Tipp auf Expertenebene. Dies sind die Arten von Tipps, auf die Sie achten möchten, wenn Sie ein echter Formel-Power-Benutzer in Excel werden möchten . Und was ich Ihnen zeigen werde, ist ein Ansatz, um übereinstimmende Elemente zwischen zwei oder mehr Listen zu zählen . Und um dies zu tun, werden wir diese einige Produkt mit der Zählfunktion kombinieren, um die Anzahl der übereinstimmenden Werte in diesen Listen jetzt zurückzugeben , in dieser Demo werden zwei verschiedene Tabellen haben. Arrangiert Produkte, Produktliste A und B. Sie können unterschiedliche Anzahl von Artikeln in ihnen haben, aber wir wissen, dass es einige Überschneidungen zwischen ihnen gibt, und das Ziel ist es, eine Zellenformel zu verwenden, um zu identifizieren und zu zählen die sich überlappen. Also, was wir tun werden, ist ein paar Punkte hier zu berechnen. Die Anzahl der Elemente in Liste A und Liste verwenden einfache zählen Formeln. Und dann ist hier der Schlüssel. Wir berechnen die tatsächliche Anzahl der übereinstimmenden Artikel. Mit anderen Worten, die Überlappung zwischen Liste A und B. Also, was hier los ist, ist die Zählung. Wenn Funktion die Rose in einer der Listen iteriert oder durchläuft und überprüft, ob eine Übereinstimmung in der anderen Liste vorhanden ist. Für jede Straße, die eine Übereinstimmung findet, wenn Zeichen an einer und für jede Zeile, in der eine Übereinstimmung nicht existiert, eine Null, und von dort summiert sich die einige Produktfunktion im Grunde nur. Das resultierende Array von Werten und diese Summe gibt uns die Anzahl der übereinstimmenden Elemente. Also eine kurze Notiz hier in diesem Fall referenzierte Liste eine erste, gefolgt von Liste B. Sie können die Reihenfolge tauschen, spielt keine Rolle. Beide Listen können als Grundlage verwendet werden. Also ein paar häufige Anwendungsfälle hier, wie wir hier zeigen, nur die Überlappung zwischen zwei Listen und Excel berechnen oder bestätigen, dass Listen eindeutig sind, um sicherzustellen, dass Sie jede Art von Doppelzählungsfehlern vermeiden . Also lassen Sie uns voran und springen in die Pro-Tipps Arbeitsmappe und tatsächlich üben, eine dieser einige Produkt gezählt Funktionen schreiben . Alles klar, also in Ihrer Protestarbeitsmappe, suchen wir nach dem Zählen passender Artikel Demo Green Tab in unserem Formel-Tipps Abschnitt, gehen Sie weiter und drücken Sie Link und springen Sie zu unseren Produktlisten hier und blättern Sie durch. Sie können sehen, wir haben zwei Listen von Produkten. Ah, Produkt Eine Liste ist etwa 100 Einheiten lang. I Liste B ist etwa 89 oder 90. In der Tat können wir Zähl-Tag-Funktionen verwenden, nur um diese beschreibenden Statistiken zurückzugeben. So zählen Sie einen Anruf ihn ein minus der Kopfzeile. Es wird uns 100 Artikel geben und Spalte A und dann denselben Prozess. Sie hier ZählenSie hiereine Spalte B abzüglich der Kopfzeile. 89 Artikel melden Rindfleisch. Nun, eine Sache, die uns helfen wird, Justus faras Lesbarkeit ist besorgt, diese Listen Eigennamen zu geben. Anstatt also auf eine Zwei durch eine 101 in einer Formel zu verweisen, möchte ich dies so etwas wie Liste A benennen, damit ich das erste Element auswählen kann, halten Sie den Schaltpfeil nach unten gedrückt, um diese vollständige Liste und im Namensfeld hier links von der Formel Bar, werde ich eine Liste in einem Eigennamen eingeben. Mach das gleiche mit diesem Be Control Shift Pfeil nach unten in dieser Liste B. Das wird nur unsere Formeln ein wenig einfacher zu arbeiten machen, wenn wir fortfahren. Also, bevor ich voran und beginne, eine Funktion hier zu füllen und e fünf zu verkaufen. Die Art und Weise, wie ich diese fortgeschritteneren Techniken lerne, ist, mit den Komponenten zu beginnen , diese wirklich, wirklich gut zu verstehen und sie dann zusammen zu dem Endergebnis zu montieren. Also lasst uns weitermachen und das tun. Ich denke, das wird hilfreich sein. Das ist richtig. Klicken Sie auf und fügen Sie einfach eine Spalte hier hinzu. Das wird wie ein leerer Arbeitsbereich sein. Und wenn wir die einige Produktkomponente für jetzt ganz ignorieren und uns nur darauf konzentrieren, was die Zählung, wenn Frieden tut, lasst uns versuchen, bilanzierte Funktion direkt hier zu schreiben, und der erste Bereich wird Liste A sein und wie Sie mit der Eingabe beginnen, werden Sie sehen, dass benannte Bereich Auffüllen ungerechter Tab es in. Und so werden wir die Elemente in Liste A zählen, die einem bestimmten Kriterium entsprechen, was in diesem Fall mit Selby beginnen auchmit Selby beginnenwird. Schließen Sie es ab, drücken Sie die Eingabetaste. Und jetzt, wenn wir diese Formel nach unten ziehen oder doppelklicken, um sie anzuwenden sehen Sie eine Reihe von Einsen und Nullen bis zum Ende von Liste A. Und was passiert hier in jeder Zeile ist, dass die Zählung? Wenn Funktion sagt, Okay, existiert B in Liste A. Ja oder nein? Wenn ja, geben Sie eine Eins zurück, wenn keine Null zurückgibt. Und diese Referenzen änderten sich zu drei, bevor sie fünf waren. Im Wesentlichen spiegelt jeder, den Sie hier in Spalte c sehen, eine Übereinstimmung wider. Und in der Tat, wenn wir alle Spalte C oder den spezifischen Bereich auswählen und auf die Sonne schauen, ist diese Summe 33, was theoretisch sagen sollte uns die Gesamtzahl der Übereinstimmungen zwischen diesen beiden Listen. Nun ist eine Sache zu beachten, dass ich nicht nur auf hier verweisen muss. Es kann diesen Verweis auf die gesamte Liste ändern. Seien Sie Referenz und drücken Sie die Eingabetaste und beobachten Sie Was passiert, wenn ich das unten anwenden? Durchklicken ändert sich nichts. Sie sehen nicht, dass sich die Formel überhaupt ändert. Aber der eigentliche Verweis auf den Wert, den wir vergleichen möchten, ändert sich Zeile für Zeile. Also bekommen wir immer noch diese Nummer. 33 Übereinstimmungen zwischen den beiden Listen und jetzt eine andere Sache. Wenn ich die Reihenfolge der Listen hier in Spalte D ausgetauscht Gleiche Annäherungszahl. Wenn diese Zeitliste zuerst ist, gefolgt von Liste A. Sie sehen ein anderes Muster von Nullen und Einsen, weil wir jetzt nach den Elementen suchen . Registrieren Sie A und ordnen Sie sie zu Liste B, aber überprüfen Sie es. Die Summe, wenn Sie den gesamten Bereich auswählen, ist immer noch 33, weshalb ich sagte, dass Sie beide Listen als Basis verwenden können . Es ist also erwähnenswert, dass Sie genau dort anhalten und eine normale traditionelle Funktion Zehe verwenden könnten . Fügen Sie diese Werte hinzu und erhalten Sie die übereinstimmende Artikelnummer von 33. Aber wir werden die Dinge noch einen Schritt weiter gehen und diesen Wert schaffen. Berechnen Sie diesen Wert, ohne diese beiden Hilfsspalten verwenden zu müssen. Stattdessen werden wir ein Produkt verwenden, das im Wesentlichen diese Arrays innerhalb der Formel selbst erstellen könnte , um dasselbe zu berechnen und diesen Wert von 33 zurückzugeben. Zeig dir, wie das aussieht, und verkaufe F fünf. Wir fangen mit dem Produkt an. Und das Array, das in diesem Fall summiert wurde, ist buchstäblich das Array, das wir gerade eine der Versionen erstellt haben, die auf der Zählung basiert. Wenn ja, können wir die gleiche Funktion eingeben, die wir gerade innerhalb der Anzahl einiger Produktfunktionen gemacht haben. Wenn Liste. Ein Kriterium ist Liste. Sei wieder. Bestellen Sie entweder. Schließen Sie es aus und drücken Sie die Eingabetaste. Da gehst du. 33 Elemente jetzt können wir tatsächlich löschen diese Hilfspalten. Es spielt keine Rolle. Wirkt sich überhaupt nicht auf die Formel aus. Wir erhalten immer noch den richtigen Wert von 33. Nun, für diejenigen von Ihnen, die nicht vertraut sind mit zeichnet ein Rennen Stil Formeln. Sie können hier irgendwie kratzen Ihren Kopf ein wenig, weil wir im Wesentlichen nur eine Formel erstellt haben , die sich in einer sehr einzigartigen und ungewöhnlichen Art und Weise richtig verhält. Es erstellt ein eigenes Array von Werten hinter den Kulissen und verarbeitet dann dieses Array, um einen einzelnen Wert zu erzeugen. Nun, als ich das erste Mal über Array-Funktionen lernte, war eine Sache, die ich sehr hilfreich fand, die Verwendung des Berechnungsformelwerkzeugs. Und was uns das hilft, ist, Dinge zusammenzubrechen und zu diagnostizieren, wie all diese Teile zusammenarbeiten. Also fangen wir von innen nach außen an. Dies ist also eine verschachtelte Funktion, und wir werden dieses Stück für Stück auswerten. Also Liste A, die das Bereichsargument der Zählung ist. Wenn es sich um ein einfaches s handelt, wird der Zellbereich, der auf eine Zwei durch eine Eins und Liste B verwiesen hat, unsere Liste von Kriterien sein. Und was wir sehen sollten, wenn wir diese Liste bewerten, ist Argument ein tatsächliches Array, das jedes Produkt oder jedes Element in dieser Liste enthält, was genau das ist, was wir hier sehen. 89 einzelne Elemente alle in einem Strahl verpackt, und dann die nächste unterstrichene Komponente ist die gesamte gezählte Funktion. Nun, da wir beide Argumente ausgewertet haben, können wir die Zählung bewerten, wenn sich selbst und überprüfen Sie dies. Dieses Rennen sollte sehr vertraut aussehen, weil es genau die gleiche Siris von 89 Einsen und Nullen , die wir durch das Schreiben von Konto generiert. Wenn die Zellformel bei der Anwendung auf 89 mal oder 2 89 stieg, die Unterschiede tun dies hier als Teil einer Produkt-Funktion und im Wesentlichen ersetzen diese 89 Funktionen durch eine. So fängt es an, Ihnen ein Gefühl dafür zu geben, warum diese Air a Stil Funktionen sind so mächtig Jetzt. Letztes Stück hier ist, dass einige Produkte in diesem Fall, wir verwenden ein Produkt in einer ziemlich einfachen Art und Weise. Wir verwenden eigentlich nicht einmal den Produktteil davon, weil wir es nur ein einzelnes Array füttern , all die einige Produkte tun, ist, diese Werte hinzuzufügen. Ich habe einen Tipp. Wir werden über Sie Niks und Duplikate sprechen, wo wir eine Ebene der Komplexität hinzufügen . Aber in diesem Fall, halten Sie es einfach. Ich füttere nur das einige Produkt, dieses eine Array, addiere die Einsen und Nullen und wir bewerten diesen letzten Schritt. Da gehst du. Wir bekommen unsere 33 und das sollte es jetzt tun. Für diejenigen von Ihnen, die mit Array-Funktionen vertraut sind, liegt die Frage, die ich garantiere, in einigen Ihrer Köpfe ist, warum haben Sie nicht Control-Shift verwendet ? Geben Sie ein, wenn Sie dies ein Produkt eingeben, das Sie Honore Funktion aufrufen. Und die Antwort ist, dass ein Produkt eine der wenigen Funktionen in Excel ist, die sich wie eine Array-Funktion verhält , aber nicht den Steuerverschiebungsansatz erfordert. So wie ich Ihnen gezeigt habe, betätigt einfach die Eingabetaste und es verhält sich wie jede andere Funktion. In diesem Fall könnte ich das einige Produkt tatsächlich durch ein einfaches ersetzen und tatsächlich den Array-Ansatz verwendet und dies mit Steuerverschiebung eingeben und ich werde die richtige Antwort erhalten, denn wieder sind wir nicht unter Verwendung der Produktkomponente eines Produkts. Jeder Ansatz ist also völlig gültig. Ich ziehe tatsächlich den Produktansatz und die Steuerung z vor, um darauf zurückzukommen, nur weil zurückzukommen ich mich nicht daran erinnern möchte, dass ich Steuerschieber eingeben muss. Ich würde es lieber wie eine normale Formel behandeln. Also da hast du es. Tolle Demo zeigt, wie Sie diese Tools wie ein Produkt verwenden können und zählen, wenn Sie wirklich mächtige Dinge in Excel zu tun . 15. Duplikate mit SUMPRODUCT zählen: Alles in Ordnung für diesen Profi-Tipp. Ich möchte ein wenig über Zählen, Duplizieren oder einzigartige Rose in einer Liste mit Selbstformeln sprechen . Jetzt ist es wichtig zu beachten, dass Excel eine Reihe von verschiedenen Möglichkeiten bietet, doppelte und eindeutige Werte zu identifizieren oder zu zählen. Sie könnten erweiterte Filter verwenden. Sie können Duplikate entfernen und die Anzahl der Zeilen zählen. Sie können Ihre Daten in eine Pivot-Tabelle anschließen und sich verschiedene Elemente ansehen oder sogar Tools wie Power Query verwenden , die verschiedene Funktionen in Anzahl integriert haben. Aber bis es eine äquivalente Formel gibt, die eindeutig zu zählen kann dies mit Zellformeln allein trügerisch schwierig sein. Aber ein wirklich faszinierender Ansatz, den ich teilen möchte, ist, ein Produkt zu verwenden und zu zählen , wenn man diese einzigartigen Werte zählt und dann von der Summe subtrahiert, um die Anzahl der Duplikate zu erreichen . Definitiv nicht der einfachste Weg, um zum Endergebnis zu kommen, aber dennoch ein faszinierender. Was wir uns hier ansehen werden, ist meine Lebensmittelliste, die stetig wächst. Ich vergesse, welche Artikel ich dort angelegt habe, und infolgedessen kaufe ich Duplikate. Was ich hier tun möchte, ist, diese Liste zu analysieren und Zellfunktionen zu verwenden, um die Gesamtzahl der Elemente zu berechnen , die in dieser Liste entstanden sind, die Anzahl der eindeutigen und Duplikate stieg in Fällen, in denen ich das gleiche Produkt zweimal aufgeschrieben habe . Um dies zu tun, werden wir ein paar verschiedene Funktionen für die Gesamtheit der Elemente verwenden würden Count Day. In diesem Fall haben wir den Zellbereich vorher bis B 52 als Lebensmittel definiert. Nun, der knifflige ist dieser zweite Einzelteil hier und um die einzigartigen zu berechnen, also werden wir eine Kombination aus einem Produkt verwenden und zählen, wenn ich das aufschlüsseln werde und Ihnen genau zeigen , wie es in unserer Demo funktioniert. Sobald Sie diese beiden Teile haben, können wir einfach den Unterschied nehmen, um die Anzahl der Duplikate zu erreichen. Also wieder, nicht mein empfohlener Ansatz, notwendigerweise als alltägliches Werkzeug, sondern eine gute Möglichkeit, genau zu sehen, wie Funktionen wie dieses, besonders wie ein Produkt, wirklich funktionieren. So verschiedene Anwendungsfälle hier. Es kann verwendet werden, um die Anzahl der eindeutigen oder eindeutigen Produkte zu berechnen, zum Beispiel, oder Sie können so etwas verwenden, um Fehler zu helfen, überprüfen, um Inflation zu vermeiden, die durch doppelte Datensätze verursacht werden könnte. Also lassen Sie uns in unsere Excel Arbeitsmappe springen und sehen, ob wir eine dieser einige Produkt-gezählt Funktionen schreiben können . In Ordnung, also werden wir suchen, sind einzigartig zu zählen und Duplikate Demo. Eine grüne Registerkarte innerhalb des Bereichs Formeltipps kann direkt aus dem Inhaltsverzeichnis mit ihm verknüpft werden. Und hier können Sie sehen, wie meine Lebensmittelliste runter zu Roe 52 geht und das erste, was ich tun werde , bevor ich eine einzige Funktion schreibe, ist, geben Sie diese Lebensmittelliste Bereich und Namen. Klicken Sie auf den Umschaltpfeil des ersten Elementsteuerelements nach unten, um das letzte Element zu erfassen, und geben Sie ihm im Namensfeld rechts neben der Bearbeitungsleiste einen Namen wie Lebensmittel. Das wird es viel einfacher machen, diese Liste in unserer Formel zu referenzieren. Namen bewegen sich vorwärts. Erste Dinge zuerst. Gesamtzahl der Elemente. Das ist eine einfache ist mit einer Zählung eine Funktion zählen einen Rat, nicht leere Zellen in einem Bereich und arrangieren wird, dass Lebensmittel Liste sein, die wir gerade Tab erstellt haben, die in der Nähe es aus, drücken Sie die Eingabetaste. Also habe ich 49 Artikel in dieser Liste, 49 Rose auf meiner Einkaufsliste. Jetzt ist der Schlüssel zu versuchen, herauszufinden, wie man berechnet, quantifiziert die Anzahl der eindeutigen spezifisch und genau wie ich Ihnen mit dem passenden Artikel Tipp früher im Kurs gezeigt habe . Was ich hier tun werde, ist, diesen Ansatz in seine kleinsten, meisten Komponententeile zu zerlegen . Verstehen Sie genau, wie die Mechanik dieser Teile Luft funktioniert, und dann werden wir sie wieder in unsere endgültige Formel zusammensetzen. Also lassen Sie uns weiter gehen und fügen Sie zwei neue Spalten hier in einfügen zu Collins links von tief. Und das wird wie unsere Sandbox sein, mit der wir hier spielen können. Und wir werden mit Konto wenn Funktion beginnen und wir werden count verwenden, wenn einfach die Anzahl der Instanzen jedes Elements innerhalb der gesamten Liste zurückgegeben wird. Also, um das zu tun, sind wir in einem Typ Anzahl Wenn und unser Sortiment ist die gesamte Liste Lebensmittel und für jetzt, für Kriterien, wählen Sie das erste Element in der Liste. Schließen Sie die Klammer. Drücken Sie die Eingabetaste Jetzt. Was diese Zahl uns sagt, ist die Anzahl der Instanzen dieses Produkts aus der gesamten Lebensmittelliste und gleich neben der Fledermaus, wir haben ein Duplikat hier. Dies sagt mir, dass grüne Band Dosenpfirsiche erscheint zweimal in der Liste. Wenn wir dies auf die gesamte Liste ziehen, sehen wir die Anzahl der Instanzen, die mit jedem einzelnen Artikel in der Lebensmittelliste verknüpft sind. So erscheint Washington Cranberry-Saft nur, wenn Mangogetränk zweimal erscheint. In der Tat, hier ist die zweite Instanz von Washington Mangogetränk, auch mit dem Etikett von to. Und dann, irgendeinem verrückten Grund, sieht es so aus, als hätte ich vier verschiedene Male den Club-Streichkäse betreten. Ich glaube, ich hätte wirklich Hunger nach Käse gehabt. Wie Sie sehen können, hat uns diese neue Spalte im Grunde die Anzahl der Instanzen jedes Produkts in unserer Liste mitgeteilt. Nun, eine kleine Optimierung, die wir hier zu einem hilfreichen Tipp machen können, ist, dass wir vorher nicht wirklich referenzieren müssen . Wenn wir die gesamte Liste erneut referenzierten und diese nach unten anwenden. Obwohl wir nicht sehen, dass sich die Referenz Zeile für Zeile ändert, erreicht sie genau das Gleiche. Es iteriert, Element für Element und zählt die Anzahl der Instanzen. Nun, das ist großartig. Es ist hilfreich, die Anzahl der Instanzen zu haben, aber es bringt uns nicht den ganzen Weg dorthin als nächsten Schritt. Was wir tun müssen, ist es tatsächlich eine neue Berechnung hier zu erstellen und nehmen Sie eine und teilen Sie sie durch den Wert, den wir gerade mit dieser gezählten Funktion erstellt haben, und ich zeige Ihnen genau, warum wir das in nur einer Sekunde tun. Lassen Sie uns es den ganzen Weg hinunter auf die vollständige Liste anwenden, und ich schaue. Was passiert, wenn Sie anfangen zu bemerken, ist, dass wir im Wesentlichen jedem eindeutigen Artikel in der Liste jedem einzelnen Produkt einen Wert von eins zuweisen . Das bedeutet, dass die Gegenstände, die nur einmal angezeigt werden, wie Jeffers, Haferflocken oder Queen City Karte, wir erhalten hier einen Wert von eins, da eine geteilt durch eins gleich eins ist. Aber der Haken ist, dass für Artikel, die mehr als einmal erscheinen, wir im Wesentlichen nehmen diese Gutschrift von einem für das Produkt und verteilen es unter der Anzahl der Instanzen. Also taucht Washington Mangogetränk zweimal auf, und jeder dieser Fälle bekommt einen halben Kredit oder 0,5. Auf diese Weise werden wir, wenn wir diese Spalte zusammenfassen, nicht jedes Produkt doppelt zählen, das mehr als einmal erscheint. In der Tat, für Fälle, in denen wir zwei oder vier Instanzen haben. Diese Fraktionen insgesamt werden nie mehr als einem entsprechen. Wenn wir also die gesamte Liste auswählen, können wir unten sehen, hier sind einige sind 44. Das ist die goldene Zahl. Das ist unsere Anzahl der tatsächlichen Einzelteile oder stieg in unserer Lebensmittelliste, so konnten wir einfach die Summe nehmen und stecken, dass einige Funktion hier rein und damit gemacht werden. Aber genau wie unsere anderen Beispiele wollen wir uns hier nicht auf diese Hilfspalten verlassen müssen. Lassen Sie uns diese gezählte Funktion in ein Produkt zusammenbauen, das im Wesentlichen dieses ganze Array erstellen und alles innerhalb der Funktion selbst zusammenfassen wird. Es wird sich wie eine Array-Formel verhalten. Also lassen Sie uns voran gehen und Typ gleich Gruppen, ein Produkt. Und alles, was wir tun werden, ist die gleichen Funktionen zu schreiben, die wir gerade in den Spalten D und E verwendet hatten , aber verschachtelt sie innerhalb dieser Formel. Also ein Produkt eins geteilt durch die Anzahl. Wenn Lebensmittel Lebensmittel Komma Lebensmittel, schließen wir Klammern und drücken die Eingabetaste. Und da haben Sie es, 44 Unikate oder Rose in unserer Liste. Und jetzt, wo wir wissen, die Gesamt und die einzigartige Schwächung nehmen einfach den Unterschied und kommen auf die Anzahl der doppelten Rose. Also bekommen wir Nummer fünf hier. Das bedeutet, wir hatten eine doppelte Reihe für Dosenpfirsiche, ein Duplikat für Mangogetränk, und diese drei Duplikate Streichkäse stieg auch auf unserer Liste, addieren sich zu kämpfen. Also, jetzt kann ich D und E löschen, und wir werden immer noch unseren richtigen Wert bekommen. Und da hast du es. Also sicherlich nicht der einfachste oder schnellste Weg, um diese Zahl zu erreichen, aber eine wirklich gute Gelegenheit, einige dieser fortschrittlicheren Funktionen zu üben, wie einige Produkte. 16. Viele Many-to-Many Lookups: Alles klar. Dieser nächste Tipp ist für diejenigen von Ihnen, die versuchen, tief in Experten-Level-Gebiet mit Ihren Excel-Formeln und -Funktionen zu tauchen . Wir werden darüber sprechen, wie Count verwendet wird, wenn zusammen mit Index und Match, um zu helfen, viele zu viele Nachschlagearbeiten und Excel. Nun, im Allgemeinen, Excel wurde entwickelt, um mit 1 zu 1 oder eine zu vielen Beziehungen zu arbeiten. Mit anderen Worten, wenn Sie versuchen, zwei Tabellen mit Nachschlagefunktionen miteinander zu verbinden oder , sollte mindestens eine dieser beiden Tabellen nur eine Instanz Ihres Nachschlagewerts oder Primärschlüssels haben . Wenn dies nicht der Fall ist, haben beide Tabellen mehrere Instanzen dieses Nachschlagewerts. Dann haben Sie eine so genannte Viele-zu-Viele-Beziehung. Um Ihnen ein Beispiel dafür zu geben, wie das aussehen könnte, werfen Sie einen Blick auf diesen Datensatz. Wir betrachten hier Umsatzdaten, eine Bestellung i D zurückverfolgt werden. In Spalte A und einem Geschäft i D und Spalte B. Und das Ziel ist es, diese Adressspalte basierend auf einer Nachschlagetabelle wie die unten stehende zu füllen . hinschauen, Wenn Sie genauhinschauen,werden Sie feststellen, dass zwei dieser Läden I. D. Nummer 5 und I. D Nummer 8 mehrere Instanzen in dieser Nachschlagetabelle haben. Und weil wir mehrere Bestellungen von jedem dieser Geschäfte verfolgt haben, wurden hier mit einer vielen zu vielen Beziehung stecken. Und um Ihnen ein Gefühl dafür zu geben, warum dies ein häufiges Problem ist. Denken Sie darüber nach, was passieren würde, wenn wir eine traditionelle V-Nachschlage- oder Indexübereinstimmungsfunktion allein für diese Beziehung verwenden, sagen Sie Excel Zehe nachschlagen Story D Nummer fünf. Es würde Spalte G und die Nachschlagetabelle durchsuchen, und es würde die erste Instanz stoppen, 59 22 LaSalle Gericht zurückgegeben und keine Ahnung, dass es jemals eine andere Instanz von Speicher I. D. Nummer fünf in an diesem Tisch. Die gute Nachricht ist, dass es Werkzeuge gibt, die wir verwenden können, um Situationen wie diese zu behandeln, und in dem Ansatz, den wir zu demonstrieren, werden wir Count verwenden. Wenn Index- und Match-Funktionen Toe tatsächlich Werte aus nachfolgenden Nachschlage-Übereinstimmungen zurückgeben. Also in diesem speziellen Fall, die Adresse aus der zweiten Instanz von Geschäft Nummer fünf oder Geschäft Nummer acht, um Ihnen ein Gefühl zu geben, wie das funktionieren wird. Lasst uns schnell auf eine dieser Zellen einarbeiten. Zelle E vier, das ist 67 64 Glen Road, Die Adresse für Geschäft Nummer acht. Und das ist die zweite Adresse in der Nachschlagetabelle. Jetzt ist der Schlüssel, um die zweite Adresse zu bekommen, dieser neue Säulensitz. Eine Reihe von Instanzen und diese Spalte wird von Konto gesteuert. Wenn Funktion. Das zählt im Grunde den Speicher I D in jeder Zeile und überprüft, wie viele Instanzen davon ich in Spalte g unserer Nachschlagetabelle existiere. Für die meisten dieser Speicher I DS ist die Anzahl der Instanzen und Spalte C eins. Aber für Ideen fünf und acht kehrt diese gezählte Funktion zu zurück, da ihre beiden Instanzen von jedem dieser I DS und was diese Information uns erlaubt, ist, weiterhin einen Index und eine Matchfunktion zu schreiben, wie wir es normalerweise tun würden. Aber anstatt beim ersten Spiel des Ladens zu stoppen, konnte ich D in Fällen, in denen es mehrere Instanzen davon gibt, zu springen , ich d in der Suche nach oben. Also in diesem Fall passen wir Store I D. Nummer acht an, springen eine zusätzliche Zeile nach unten und ziehen die resultierende Adresse. 67 64 Glen Road. Jetzt sehr wichtige Notiz hier. Sie müssen verstehen, warum es mehrere Instanzen Ihres Schlüssels gibt, und es ist sehr wichtig zu bestätigen, dass es sich tatsächlich gültige Datensätze handelt. Denn oft, wenn Menschen auf so viele zu viele Beziehungen stoßen, ist es ein Hinweis auf ein tieferes Problem mit ihrer Datenbank In diesem Fall werden Formel-Workarounds wie diese wirklich nicht helfen, das Root-Problem zu lösen. Das heißt, es gibt einige häufige Anwendungsfälle, die Situationen wie diese schaffen. Ein Beispiel ist, wenn Sie historische Änderungen in einer vorhandenen Nachschlagetabelle verfolgen. Vielleicht haben Sie also ein Produkt, dessen Preis sich im Laufe der Zeit verändert hat, in diesem Fall haben Sie das gleiche Produkt i d. Aber eines seiner Attribute, in diesem Fall hat sich der Verkaufspreis geändert. Oder vielleicht in dem Fall, dass wir uns hier ansehen, speichern Sie Nummer fünf im Geschäft Nummer acht bewegte Standorte, aber wir wollen es als die gleiche Entität verfolgen. Beide sind potenziell machbare Erklärungen dafür, warum wir in eine Beziehung wie die, die wir hier sehen, laufen könnten . uns also Lassen Sieuns alsoin Excel springen. Öffne unsere Pro-Tipp-Arbeitsmappe und lass uns sehen, was wir mit einer dieser vielen zu vielen Beziehungen tun können . In Ordnung, also aus unserem Inhaltsverzeichnis gehen wir zum Abschnitt „Grüne Formel-Tipps“. Suchen Sie nach der Demo von vielen bis vielen Lookups. Gehen Sie weiter und verknüpfen Sie direkt mit dem Blatt. Und hier haben wir Umsatzverfolgung basierend auf Bestellung I DS Karte zu einem bestimmten Geschäft. Ich d hier in Spalte B und wie wir darüber gesprochen haben, werden wir versuchen, diese Adressspalte mit der Nachschlagetabelle hier in Collins, F und G zu füllen . Aber denken Sie daran, Aber denken Sie daran, der Haken ist, dass wir hier fünf Geschäfte haben und acht mit doppelter Rose in der Nachschlagetabelle. Wenn wir zum Beispiel eine traditionelle Indexübereinstimmungsfunktion schreiben würden, werden wir indizieren, dass der Bereich direkt von F G nachschlägt, sperren Sie ihn in die Zeilennummer, zu der wir bewegen möchten, ist, wo immer in der Lage waren, mit diesem Speicher übereinzustimmen Ich D und schlug zu und aus Gewohnheit. Ich werde die Spalte B sperren und wo wir versuchen, diese Wunde zu passen? Ich D. Nun, wir versuchen, es in der Liste der Speicher I ds in Spalte f passen Sperren Sie es in, kommen über genaue Übereinstimmung und Kleidung, die übereinstimmen funktionieren aus. Also indizieren wir diesen Bereich. Wir gehen runter zu der Zeile, in der wir mit dem Laden i D übereinstimmen und die Spalte, die wir in diesem Fall wollen, ruft Nummer zwei an. Und das sollte es für die Indexübereinstimmungsfunktion Drücken Sie. Geben Sie ein und wenden Sie es nach unten. Und genau wie Sie es erwarten, erhalten wir die richtigen Werte für Geschäfte. 1234679 und 10. Start, um Kapitäne sind weg. Lagern Sie sechs. Mitchell Canyon. Hier ist der Fang Store acht Buenavista, das ist die erste Instanz. Speichern Sie fünf LaSalle wieder erste Instanz. Also auf eigene Faust dieser Index Match-Funktion gleich mit dem V. Suchen Sie nach oben. Hatte schwach auf dieser Route hat keine Ahnung, dass diese Rose sogar in der Nachschlagetabelle existieren. Also müssen wir eine weitere Information erstellen, um uns dabei zu helfen, dies zu berücksichtigen. Und das ist, wo diese Instanzspalte ins Spiel kommt. Also werde ich es hinzufügen. Rufen Sie ihn hier zwischen B und D. Nennen Sie es die Anzahl der Instanzen und es ist ein einfaches Konto ist, wenn Funktion und der Bereich , in dem gezählt wurden, der Speicher I d Bereich von der Suche nach oben ist. Wir werden das in den Kriterien sperren. Was wir versuchen, innerhalb dieses Bereichs zu zählen, ist unser Geschäft I d. In diesem Fall Selby auch Selby. Schließen Sie es aus, geben Sie ein und ziehen Sie es nach unten. Und da gehen wir hin. Also jetzt haben wir effektiv den Store I DS markiert oder beschriftet, der mehr als einmal auftaucht. Alles hier drin ist ein Eins, mit Ausnahme unserer Achtel und sind Fünf, genau das, was wir hier erwarten würden. Und jetzt, da wir diese zusätzliche Information haben, können wir diese Daten nutzen und sie in unsere Index-Match-Funktion integrieren, um eine zusätzliche Zeile nach unten zu springen , aber nur für die Speicher I DS Onley für fünf und acht. Also, wie können wir das tun? Nun, wir schauen uns ihre Indexfunktion an. Das Zeilennummern-Argument, das ist, wo unsere Match-Funktion lebt, ist das, was Excel sagt, aus welcher Straße Daten gezogen hat. Also müssen wir einen Wert nach dieser Übereinstimmungsfunktion erhöhen, um eine zusätzliche Zeile vier speichert fünf und acht nach unten zu springen , und wir müssen dies tun, indem wir diese neue Anzahl von Instanzen Spalte verwenden, die wir gerade in Spalte Cerstellt gerade in Spalte C Aber wenn wir nur den Wert aus Spalte C hinzufügen, werfen Sie einen Blick auf, was das tun wird. Es ist tatsächlich gehen, um eine zusätzliche Reihe für jeden einzelnen Laden und zwei zusätzliche Rose für Geschäfte fünf und acht springen . Und wenn wir das tun würden, wie Sie sehen können, wird alles weggeworfen. Also Store to kehrt jetzt zurück. Ramsey Circle , der ein außerhalb des Stores 10 ist, gibt einen Ref-Fehler zurück, weil er ihn außerhalb des Nachschlagebereichs schiebt . Und dann für fünf. Wir bekommen immer noch die falschen Antworten, denn jetzt finden wir die erste Instanz von fünf und springen zu mehr Reihen nach unten, was mehr ist, als wir brauchen. Also mit einer schnellen Anpassung hier, alles, was wir tun müssen, ist, anstatt C zwei hinzuzufügen, werden wir C zwei minus eins hinzufügen. Schließen Sie die Klammer und drücken Sie die Eingabetaste und überprüfen Sie das jetzt. Perfekt. Jetzt geben alle 1234679 10 unseres anderen Stores die richtigen Werte zurück. Und jetzt sieh dir diesen Laden an. Fünf für 90 Risdon Road, das ist die letzte oder zweite Instanz von I D Nummer fünf in diesem Nachschlagen Nummer acht Glen Road Genau das, was wir suchen, so können Sie weiterhin anpassen, so viel wie Sie möchten. Sie können zusätzliche Logik hinzufügen. Sie können zusätzliche Funktionen verschachteln, aber hoffentlich gibt Ihnen dies eine Vorstellung davon, wie einige dieser Tools, wie zählen ifs und Index und Match, verwendet werden können, um Fälle aufzunehmen, in denen Sie viele zu viele Beziehungen wie diese haben könnten .