3 verrückte Microsoft Excel-Formeln, die äußerst nützlich sind
Microsoft Excel-Formeln können fast alles. In diesem Artikel erfahren Sie anhand von drei nützlichen Beispielen, wie leistungsfähig Microsoft Excel-Formeln und bedingte Formatierung sein können.
Kennenlernen von Microsoft Excel
Wir haben eine Reihe verschiedener Möglichkeiten behandelt, um Excel besser zu nutzen, z. B. wo Sie großartige herunterladbare Excel-Vorlagen finden und wie Sie Excel als Projektmanagement-Tool verwenden können .
Ein Großteil der Excel-Leistung liegt hinter den Excel-Formeln und -Regeln, die Ihnen helfen, Daten und Informationen automatisch zu bearbeiten, unabhängig davon, welche Daten Sie in die Tabelle einfügen.
Sehen wir uns an, wie Sie Formeln und andere Tools verwenden können, um Microsoft Excel besser zu verwenden.
Bedingte Formatierung mit Excel-Formeln
Eines der Tools, die die Leute nicht oft genug verwenden, ist die bedingte Formatierung. Mithilfe von Excel-Formeln, Regeln oder nur wenigen wirklich einfachen Einstellungen können Sie eine Tabellenkalkulation in ein automatisiertes Dashboard verwandeln.
Um auf Bedingte Formatierung, klicken Sie einfach auf die Registerkarte Start und klicken Sie auf das Bedingte Formatierung Symbol in der Symbolleiste.
Unter Bedingte Formatierung gibt es viele Optionen. Die meisten davon würden den Rahmen dieses speziellen Artikels sprengen, aber die meisten davon betreffen das Hervorheben, Einfärben oder Schattieren von Zellen basierend auf den Daten in dieser Zelle.
Dies ist wahrscheinlich die häufigste Verwendung der bedingten Formatierung – Dinge wie das Rotieren einer Zelle mit Kleiner-als- oder Größer-als-Formeln. Erfahren Sie mehr über die Verwendung von IF-Anweisungen in Excel .
Eines der weniger verwendeten Tools zur bedingten Formatierung ist die Option Symbolsätze , die eine Reihe von großartigen Symbolen bietet, mit denen Sie eine Excel-Datenzelle in ein Dashboard-Anzeigesymbol verwandeln können.
Wenn Sie auf Regeln verwalten klicken, gelangen Sie zum Manager für Regeln für bedingte Formatierung .
Abhängig von den Daten, die Sie vor der Auswahl des Symbolsatzes ausgewählt haben, sehen Sie im Manager-Fenster die Zelle mit dem gerade ausgewählten Symbolsatz.
Wenn Sie auf Regel bearbeiten klicken, sehen Sie den Dialog, in dem die Magie passiert.
Hier können Sie die logische Formel und Gleichungen erstellen, die das gewünschte Dashboard-Symbol anzeigen.
Dieses Beispiel-Dashboard zeigt die für verschiedene Aufgaben aufgewendete Zeit im Vergleich zur budgetierten Zeit. Wenn Sie das halbe Budget überschreiten, wird ein gelbes Licht angezeigt. Wenn das Budget vollständig überschritten ist, wird es rot.
Wie Sie sehen, zeigt dieses Dashboard, dass die Zeitbudgetierung nicht erfolgreich ist.
Fast die Hälfte der Zeit wird weit über den budgetierten Beträgen aufgewendet.
Zeit, sich neu zu konzentrieren und Ihre Zeit besser zu verwalten!
1. Verwenden der VLookup-Funktion
Wenn Sie erweiterte Microsoft Excel-Funktionen verwenden möchten, finden Sie hier einige zum Ausprobieren.
Sie kennen wahrscheinlich die VLookup-Funktion, mit der Sie eine Liste nach einem bestimmten Element in einer Spalte durchsuchen und die Daten aus einer anderen Spalte in derselben Zeile wie dieses Element zurückgeben können.
Leider erfordert die Funktion, dass sich das gesuchte Element in der Liste in der linken Spalte befindet und die gesuchten Daten in der rechten Spalte, aber was ist, wenn sie vertauscht werden?
Was ist, wenn ich im folgenden Beispiel die Aufgabe, die ich am 25.06.2018 ausgeführt habe, anhand der folgenden Daten ermitteln möchte?
In diesem Fall durchsuchen Sie die Werte auf der rechten Seite und möchten den entsprechenden Wert auf der linken Seite zurückgeben.
Wenn Sie Microsoft Excel-Pro-User-Foren lesen, werden Sie feststellen, dass viele Leute sagen, dass dies mit VLookup nicht möglich ist. Dazu müssen Sie eine Kombination aus Index- und Match-Funktionen verwenden. Das stimmt nicht ganz.
Sie können VLookup dazu bringen, auf diese Weise zu arbeiten, indem Sie eine CHOOSE-Funktion darin verschachteln. In diesem Fall würde die Excel-Formel so aussehen:
"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"Diese Funktion bedeutet, dass Sie das Datum 25.06.2013 in der Nachschlageliste suchen 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, ist die Spalte in der obigen Tabelle tatsächlich 1, richtig?
Das stimmt, aber mit der Funktion " CHOOSE " manipulieren Sie die beiden Felder.
Sie weisen Datenbereichen Referenz-"Index"-Nummern zu – ordnen Sie die Daten der Indexnummer 1 und die Aufgaben der Indexnummer 2 zu.
Wenn Sie also "2" in die VLookup-Funktion eingeben, beziehen Sie sich tatsächlich auf die Indexnummer 2 in der CHOOSE-Funktion. Cool, oder?
Die VLookup verwendet jetzt die Datumsspalte und gibt Daten aus der Spalte Task, obwohl Aufgabe auf der linken Seite.
Jetzt, da Sie diesen kleinen Leckerbissen kennen, stellen Sie sich vor, was Sie sonst noch tun können!
Wenn Sie andere erweiterte Datensuchaufgaben ausführen möchten, lesen Sie diesen Artikel zum Suchen von Daten in Excel mithilfe von Suchfunktionen .
2. Verschachtelte Formel zum Analysieren von Strings
Hier ist eine weitere verrückte Excel-Formel für Sie! Es kann Fälle geben, in denen Sie entweder Daten aus einer externen Quelle in Microsoft Excel importieren, die aus einer Zeichenfolge von Daten mit Trennzeichen besteht.
Nachdem Sie die Daten eingegeben haben, möchten Sie diese Daten in die einzelnen Komponenten zerlegen. Hier ist ein Beispiel für Name, Adresse und Telefonnummer, getrennt durch das ";" Charakter.
So können Sie diese Informationen mit einer Excel-Formel analysieren (sehen Sie, ob Sie diesem Wahnsinn geistig folgen können):
Für das erste Feld, um das Element ganz links (den Namen der Person) zu extrahieren, würden Sie einfach eine LINKS-Funktion in der Formel verwenden.
"=LEFT(A2,FIND(";",A2,1)-1)"So funktioniert diese Logik:
- Durchsucht die Textzeichenfolge von A2
- Findet das ";" Trennzeichen
- Subtrahiert eins für die richtige Position des Endes dieses Saitenabschnitts
- Erfasst den Text ganz links bis zu diesem Punkt
In diesem Fall ist der Text ganz links "Ryan". Mission erfüllt.
3. Verschachtelte Formel in Excel
Aber was ist mit den anderen Abschnitten?
Es mag einfachere Wege geben, dies zu tun, aber da wir versuchen möchten, die verrückteste verschachtelte Excel-Formel zu erstellen (die tatsächlich funktioniert), werden wir einen einzigartigen Ansatz verwenden.
Um die Teile auf der rechten Seite zu extrahieren, müssen Sie mehrere RIGHT-Funktionen verschachteln, um den Textabschnitt bis zum ersten ";" Symbol und führen Sie die LINKE Funktion erneut aus. So sieht das aus, wenn Sie den Hausnummernteil der Adresse 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 zusammenzusetzen. Ich habe nur diese Funktion übernommen:
RIGHT(A2,LEN(A2)-FIND(";",A2))Und fügte es an jeder Stelle in der LINKEN Funktion oben ein, wo ein "A2" steht.
Dadurch wird der zweite Abschnitt der Zeichenfolge korrekt extrahiert.
Für jeden nachfolgenden Abschnitt der Zeichenfolge muss ein weiteres Nest erstellt werden. Jetzt müssen Sie nur noch die Gleichung " RECHTS ", die Sie im letzten Abschnitt erstellt haben, in eine neue RECHTE Formel einfügen, wobei die vorherige RECHTE Formel dort eingefügt wird, wo Sie "A2" sehen. 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 müssen Sie DIESE Formel nehmen und sie in die ursprüngliche LINKE Formel einfügen, wo immer ein "A2" ist.
Die endgültige bewusstseinsverändernde Formel sieht wie folgt 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 Zeichenfolge.
Um den nächsten Abschnitt zu extrahieren, wiederholen Sie den obigen Vorgang noch einmal.
Ihre Excel-Formeln können wirklich durcheinander werden, aber alles, was Sie tun, ist, lange Formeln auszuschneiden und in sich selbst einzufügen, um lange Nester zu erstellen, die immer noch 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 begrenzten Daten, und dann unter dem Menüpunkt Daten, wählen Sie Text in Spalten.
Dadurch wird ein Fenster geöffnet, in dem Sie die Zeichenfolge durch ein beliebiges Trennzeichen teilen können. Geben Sie einfach ' ; ' und Sie werden sehen, dass sich die Vorschau Ihrer ausgewählten Daten entsprechend ändert.
Mit ein paar Klicks können Sie dasselbe wie die oben genannte verrückte Formel tun … aber wo ist der Spaß daran?
Mit Microsoft Excel-Formeln verrückt werden
Da hast du es also. Die obigen Formeln beweisen, wie übertrieben eine Person sein kann, wenn sie Microsoft Excel-Formeln erstellt, um bestimmte Aufgaben zu erledigen.
Manchmal sind diese Excel-Formeln nicht wirklich der einfachste (oder beste) Weg, um Dinge zu erreichen. Die meisten Programmierer werden Ihnen sagen, dass Sie es einfach halten sollen, und das gilt für Excel-Formeln genauso wie für alles andere.
Wenn Sie mit der Verwendung von Excel wirklich ernsthaft anfangen möchten, sollten Sie unseren Einsteigerleitfaden zur Verwendung von Microsoft Excel lesen. Es bietet alles, was Sie brauchen, um Ihre Produktivität mit Excel zu steigern. Konsultieren Sie danach unseren Spickzettel für die wichtigsten Excel-Funktionen, um weitere Informationen zu erhalten.
Bildquelle: kues/Depositphotos