Von Ryan Dube im Oktober 2017 aktualisiert.
Ich habe immer geglaubt, dass Microsoft Excel eines der leistungsfähigsten Softwaretools ist. Es ist nicht nur die Tatsache, dass es eine leistungsfähige Tabellenkalkulation ist. Microsoft Excel selbst verfügt über eine solch beeindruckende Sammlung von integrierten Tools und Funktionen. In diesem Artikel möchte ich Ihnen zeigen, wie leistungsfähig Formeln und bedingte Formatierung sein können, mit drei nützlichen Beispielen.
Wir haben eine Reihe verschiedener Möglichkeiten behandelt, um Excel besser zu nutzen, z. B. um eine eigene Kalendervorlage zu erstellen. So erstellen Sie eine Kalendervorlage in Excel So erstellen Sie eine Kalendervorlage in Excel Sie benötigen immer einen Kalender. Wir zeigen Ihnen, wie Sie Ihre eigene benutzerdefinierte Vorlage kostenlos mit Microsoft Excel erstellen können. Lesen Sie mehr, verwenden Sie es als Ziel-Management-Tool 10+ Nützliche Excel-Vorlagen für Projektmanagement und Tracking 10+ Nützliche Excel-Vorlagen für Projektmanagement & Tracking Vorlagen sind für Projektleiter, was Messer für professionelle Köche sind: unverzichtbar. Wir zeigen Ihnen, wie Sie erfolgreiche Projekte mit vorgefertigten Vorlagen in Microsoft Excel und darüber hinaus replizieren können. Lesen Sie mehr und andere einzigartige Möglichkeiten, mit denen Sie Ihr Leben verwalten können. Lesen Sie diese Artikel und Sie werden sehen, wie mächtig Microsoft Excel sein kann.
Ein großer Teil der Leistung liegt tatsächlich hinter den Formeln und Regeln, mit denen Sie Daten und Informationen automatisch bearbeiten können, unabhängig davon, welche Daten Sie in die Tabelle einfügen.
In Microsoft Excel eingraben
Mit den richtigen Informationen können Sie ein System erstellen, das automatisch Ergebnisse und Berichte aus diesen Rohdaten berechnet und neu berechnet. Heute möchte ich etwas weiter unter die Oberfläche gehen und Ihnen zeigen, wie Sie einige der zugrunde liegenden Formeln und anderen Tools verwenden können, um Microsoft Excel besser zu nutzen.
Cool Conditional Formatierung mit Formeln
Eines der Tools, von denen ich glaube, dass sie nicht oft genug verwendet werden, ist die bedingte Formatierung. Wenn Sie nach erweiterten Informationen zur bedingten Formatierung in Microsoft Excel suchen, lesen Sie Sandys Artikel zum Formatieren von Daten in Microsoft Excel mit bedingter Formatierung. Daten automatisch formatieren in Excel-Tabellen mit bedingter Formatierung Formatieren von Daten in Excel-Tabellen mit bedingter Formatierung Mit der Funktion für die bedingte Formatierung von Excel können Sie einzelne Zellen in einer Excel-Tabelle basierend auf ihrem Wert formatieren. Wir zeigen Ihnen, wie Sie dies für verschiedene alltägliche Aufgaben verwenden können. Weiterlesen .
Mithilfe von Formeln, Regeln oder nur wenigen wirklich einfachen Einstellungen können Sie eine Tabellenkalkulation in ein automatisiertes Dashboard umwandeln, das Ihnen auf einen Blick eine Menge über die Informationen in der Tabelle zeigt.
Um zur bedingten Formatierung zu gelangen, klicken Sie einfach auf die Registerkarte Start und dann auf das Symbol "Bedingte Formatierung" in der Symbolleiste.
Unter bedingter Formatierung gibt es eine Reihe von Optionen. Die meisten davon gehen über den Rahmen dieses speziellen Artikels hinaus, aber die meisten von ihnen befassen sich mit Hervorhebung, Färbung oder Schattierung von Zellen basierend auf den Daten innerhalb dieser Zelle. Dies ist wahrscheinlich die gebräuchlichste Form der bedingten Formatierung. Sie können beispielsweise eine Zelle rot unter Verwendung logischer Less-Than oder größer-als-Formeln drehen. Brad Jones beschreibt, wie man viele davon in seinem Artikel über das Erstellen eines Excel-Dashboards verwendet. Visualisieren Sie Ihre Daten und machen Sie Ihre Tabellen benutzerfreundlich mit einem Excel-Dashboard Visualisieren Sie Ihre Daten und machen Sie Ihre Tabellen benutzerfreundlich mit einem Excel-Dashboard Manchmal ein einfaches Tabellenformat greift nicht genug ein, um Ihre Daten zugänglich zu machen. Ein Dashboard ermöglicht es Ihnen, Ihre wichtigsten Daten in einem übersichtlichen Format darzustellen. Lesen Sie mehr - es lohnt sich, es zu überprüfen, wenn das Ihr Ziel ist.
Eine der weniger verwendeten Werkzeuge zur bedingten Formatierung ist die Option für Symbolsätze, die eine große Auswahl an Symbolen bietet, mit denen Sie eine Excel-Datenzelle in ein Dashboard-Anzeigesymbol verwandeln können.
Ich habe dies entdeckt, nachdem ich Microsoft Office 13+ aufgerüstet habe Gründe, warum Sie auf Microsoft Office 2016 upgraden sollten 13+ Gründe, warum Sie auf Microsoft Office 2016 upgraden sollten Microsoft Office 2016 ist da und es ist Zeit für Sie, eine Entscheidung zu treffen. Die Produktivitätsfrage ist - sollten Sie upgraden? Wir geben Ihnen die neuen Funktionen und die frischeren Gründe, um Ihnen zu helfen ... Read More und verwendet bedingte Formatierung in Microsoft Excel zum ersten Mal.
Ich überprüfte die Icon-Sets und sah diese coolen LED-Anzeigeleuchten, die ich bisher nur in einigen der von mir programmierten Fabrikautomatisierungsanzeigen gesehen hatte. Wenn Sie auf "Regeln verwalten" klicken, gelangen Sie zum Manager für bedingte Formatierungsregeln. Abhängig von den Daten, die Sie vor der Auswahl des Symbolsatzes ausgewählt haben, sehen Sie die im Manager-Fenster angezeigte Zelle mit dem Symbol, das Sie gerade ausgewählt haben.
Wenn Sie auf "Regel bearbeiten ..." klicken, sehen Sie den Dialog, in dem die Magie passiert. Hier können Sie die logische Formel und die Gleichungen erstellen, die das gewünschte Dashboard-Symbol anzeigen. In meinem Beispiel überwache ich die Zeit, die ich für verschiedene Aufgaben aufgewandt habe, im Gegensatz zu meiner veranschlagten Zeit. Wenn ich über die Hälfte meines Budgets gegangen bin, möchte ich ein gelbes Licht anzeigen, und wenn ich über dem Budget bin, möchte ich, dass es rot wird.
Wie du sehen kannst, budge ich meine Zeit nicht wirklich gut ein. Fast die Hälfte meiner Zeit ist weit über das hinausgegangen, was ich budgetiert habe.
Zeit, sich neu zu konzentrieren und meine Zeit besser zu verwalten Verwenden Sie die Zeitmanagementregel 80/20, um Ihre Aufgaben zu priorisieren Verwenden Sie die Zeitmanagementregel 80/20, um Ihre Aufgaben zu priorisieren Wie maximieren Sie Ihre Zeit? Wenn Sie bereits Prioritäten setzen, delegieren und immer noch darum kämpfen, alles erledigt zu bekommen, sollten Sie die 80/20-Regel ausprobieren, die auch als Pareto-Prinzip bekannt ist. Weiterlesen !
Nachschlagen von Elementen mit der VLookup-Funktion
Okay, vielleicht ist das nicht verrückt genug für dich. Vielleicht sind Sie nicht so begeistert von einfachen logischen Formeln, die Lichter ein- und ausschalten. Wenn Sie erweiterte Microsoft Excel-Funktionen verwenden möchten, dann habe ich eine andere für Sie gegangen.
Sie sind wahrscheinlich mit der VLookup-Funktion vertraut, mit der Sie eine Liste nach einem bestimmten Element in einer Spalte durchsuchen und die Daten aus einer anderen Spalte in der gleichen Zeile wie das Element zurückgeben können. Leider erfordert die Funktion, dass das Objekt, nach dem Sie suchen, in der linken Spalte und die Daten, nach denen Sie suchen, auf der rechten Seite sind. Was aber, wenn sie gewechselt werden?
Was passiert, wenn ich im folgenden Beispiel die Aufgabe, die ich am 25.06.2013 durchgeführt habe, anhand der folgenden Daten finden kann?
In diesem Fall werden die Werte auf der rechten Seite durchsucht, und Sie möchten den entsprechenden Wert auf der linken Seite ausgeben - entgegengesetzt zu der Art, wie VLookup normalerweise funktioniert. Excel-Tabellen schneller durchsuchen: SVERWEIS mit INDEX und MATCH ersetzen Excel-Tabellen schneller durchsuchen: SVERWEIS ersetzen Mit INDEX und MATCH Verwenden Sie SVERWEIS immer noch, um nach Informationen in Ihrer Tabelle zu suchen? So können INDEX und MATCH eine bessere Lösung bieten. Weiterlesen . Wenn Sie Microsoft Excel-Pro-User-Foren lesen, finden Sie viele Leute, die sagen, dass dies mit VLookup nicht möglich ist und dass Sie dafür eine Kombination von Index- und Match-Funktionen verwenden müssen. Das stimmt nicht ganz.
Sie können VLookup auf diese Weise arbeiten lassen, indem Sie eine CHOOSE-Funktion darin verschachteln. In diesem Fall würde die Formel folgendermaßen aussehen:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" Diese Funktion bedeutet, dass Sie das Datum 25.06.2013 in der Nachschlageliste finden und dann den entsprechenden Wert aus dem Spaltenindex zurückgeben möchten. In diesem Fall werden Sie feststellen, dass der Spaltenindex "2" ist, aber wie Sie sehen können, ist die Spalte in der obigen Tabelle tatsächlich 1, richtig?
Das stimmt, aber was Sie mit der Funktion "AUSWÄHLEN" tun, ist die Manipulation der beiden Felder. Sie ordnen den Datenbereichen Referenz-Indexnummern zu - die Daten werden der Indexnummer 1 und die Aufgaben der Indexnummer zwei zugewiesen. Wenn Sie also in der VLookup-Funktion "2" eingeben, beziehen Sie sich auf den Index 2 in der Funktion CHOOSE. Verrückt, was?
Jetzt verwendet VLookup die Date-Spalte und gibt die Daten aus der Task-Spalte zurück, obwohl Task auf der linken Seite ist. Jetzt, da Sie diesen kleinen Leckerbissen kennen, stellen Sie sich vor, was Sie tun können!
Wenn Sie versuchen, andere erweiterte Datensuchaufgaben durchzuführen, lesen Sie den vollständigen Artikel von Dann über das Suchen von Daten in Excel mithilfe von Suchfunktionen. Suchen Sie alles in Ihrer Excel-Tabelle mit Suchfunktionen Suchen Sie alles in Ihrer Excel-Tabelle mit Suchfunktionen in einem Riese Excel-Tabelle, STRG + F wird Sie nur so weit bringen. Sei schlau und lass Formeln die harte Arbeit machen. Suchformeln sparen Zeit und sind einfach anzuwenden. Weiterlesen .
Insane geschachtelte Formeln zum Parsen von Strings
Wie du sehen kannst, versuche ich, ein bisschen verrückter zu werden, weil wir wissen, dass es da draußen einige gibt, die denken: "Naja, das ist überhaupt nicht verrückt!" Ich weiß, deine Standards sind hoch. Ich versuche, ihnen gerecht zu werden. Hier ist eine weitere verrückte Formel für dich.
Es kann Fälle geben, in denen Sie entweder Daten aus einer externen Quelle in Microsoft Excel importieren, die aus einer Zeichenfolge mit begrenzten Daten besteht. Sobald Sie die Daten eingefügt haben, möchten Sie diese Daten in die einzelnen Komponenten auslagern. Hier sehen Sie ein Beispiel für Name, Adresse und Telefonnummer, die durch das Zeichen ";" begrenzt sind.
Hier ist, wie Sie diese Informationen mit einer Excel-Formel analysieren können (sehen Sie, ob Sie mental mit diesem Wahnsinn folgen können):
Für das erste Feld, um das Element ganz links (den Namen der Person) zu extrahieren, würden Sie einfach eine LINKE Funktion in der Formel verwenden.
"=LEFT(A2, FIND(";", A2, 1)-1)" Dies durchsucht die Textzeichenfolge von A2, findet das Begrenzungssymbol ";", subtrahiert eins für die richtige Position des Endes dieses Zeichenfolgeabschnitts und dann den Text ganz links an diesem Punkt. In diesem Fall ist das "Ryan". Mission erfüllt.
Schachteln von Excel-Formeln
Aber was ist mit den anderen Abschnitten? Nun, um die Teile auf der rechten Seite zu extrahieren, müssen Sie mehrere RECHTS-Funktionen verschachteln, um den Textabschnitt bis zum ersten ";" - Symbol zu erfassen und die LEFT-Funktion erneut auszuführen. So sieht das aus, um den Straßennummerenteil der Adresse zu extrahieren.
"=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" Es sieht verrückt aus, aber es ist nicht schwer, es zusammenzufügen. Alles, was ich getan habe, ist diese Funktion:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" Und fügte es an jeder Stelle in der LINKEN Funktion oberhalb ein, wo es ein "A2" gibt. Dies extrahiert den zweiten Abschnitt der Zeichenfolge korrekt.
Jeder nachfolgende Abschnitt der Zeichenfolge benötigt ein anderes erstelltes Nest. Jetzt nimmst du einfach die verrückte "RECHTE" Gleichung, die du für den letzten Abschnitt erstellt hast, und übergibst sie dann in eine neue RECHTS-Formel mit der vorherigen RECHTS-Formel, die überall dort eingefügt wird, wo du "A2" siehst. So sieht das aus.
"(RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))))))" Dann nehmen Sie diese Formel und legen Sie sie in die ursprüngliche linke Formel, wo immer es eine "A2" gibt. Die endgültige Formel, die den Verstand annimmt, sieht so aus:
"=LEFT((RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), FIND(";", (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), 1)-1)" Diese Formel extrahiert "Portland, ME 04076" korrekt aus der ursprünglichen Zeichenkette.
Um den nächsten Abschnitt zu extrahieren, wiederholen Sie den obigen Vorgang noch einmal. Deine Formeln können sehr wahnsinnig werden, aber du machst nur lange Formeln in sich selbst, machst lange Nester, die wirklich gut funktionieren!
Ja, das erfüllt die Voraussetzung für "verrückt", aber seien wir ehrlich ... es gibt einen viel einfacheren Weg, dasselbe mit einer Funktion zu erreichen. Wählen Sie einfach die Spalte mit den Daten mit Trennzeichen aus, und wählen Sie dann im Menü " Daten" die Option " Text in Spalten" . Dadurch wird ein Fenster geöffnet, in dem Sie die Zeichenfolge mit einem beliebigen Trennzeichen teilen können.
Mit ein paar Mausklicks können Sie dasselbe wie oben beschrieben ... aber was macht den Spaß daran?
Verrückt werden mit Microsoft Excel
So, da hast du es. Die oben genannten Formeln beweisen, wie übertrieben eine Person beim Erstellen von Microsoft Excel-Formeln zum Ausführen bestimmter Aufgaben erhalten kann. Manchmal sind diese Formeln nicht wirklich der einfachste (oder beste) Weg, um Dinge zu erreichen. Die meisten Programmierer werden Ihnen sagen, es einfach zu halten, und das ist so wahr mit Excel-Formeln 16 Excel-Formeln, die Ihnen helfen werden, reale Probleme zu lösen 16 Excel-Formeln, die Ihnen helfen werden, reale Probleme zu lösen Das richtige Werkzeug ist die halbe Arbeit. Excel kann Berechnungen schneller durchführen und Daten verarbeiten, als Sie Ihren Rechner finden können. Wir zeigen Ihnen wichtige Excel-Formeln und demonstrieren, wie Sie sie verwenden. Lesen Sie mehr als alles andere.
Waren diese Formeln und Techniken wild genug? Haben Sie irgendwelche erstaunlichen Formel-Tricks, die Ihre Microsoft Excel-Super-User-Toolbox ausmachen? Teile deine Eingaben und Feedback in den Kommentaren!
Bildquelle: kues / Depositphotos