3 verrückte Excel-Formeln, die erstaunliche Dinge tun

• Produktivität

Werbung

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 Formatierungen sein können.

Schalte jetzt den Spickzettel "Essential Excel Formulas" frei!

Hiermit melden Sie sich für unseren Newsletter an

In Microsoft Excel graben

Es gibt verschiedene Möglichkeiten, Excel besser zu nutzen, z. B. die Erstellung einer eigenen Kalendervorlage oder die Verwendung als Projektmanagement-Tool.

Ein Großteil der Leistung steckt hinter den Excel-Formeln und -Regeln, die Sie schreiben können, um Daten und Informationen automatisch zu bearbeiten, unabhängig davon, welche Daten Sie in die Tabelle einfügen.

Lassen Sie uns untersuchen, wie Sie Formeln und andere Tools verwenden können, um Microsoft Excel besser zu nutzen.

Bedingte Formatierung mit Excel-Formeln

Eines der Tools, die nicht oft genug verwendet werden, ist die bedingte Formatierung. Weitere Informationen zur bedingten Formatierung in Microsoft Excel finden Sie in Sandys Artikel zur Formatierung von Daten in Microsoft Excel mit bedingter Formatierung.

Mithilfe von Excel-Formeln, Regeln oder nur wenigen wirklich einfachen Einstellungen können Sie eine Tabelle in ein automatisiertes Dashboard verwandeln.

Um zur bedingten Formatierung zu gelangen, klicken Sie einfach auf die Registerkarte Start und dann auf das Symbolleistensymbol Bedingte Formatierung .

Unter Bedingte Formatierung gibt es viele Optionen. Die meisten davon gehen über den Rahmen dieses Artikels hinaus, die meisten befassen sich jedoch mit dem Hervorheben, Färben oder Schattieren von Zellen basierend auf den Daten in dieser Zelle.

Dies ist wahrscheinlich die häufigste Verwendung der bedingten Formatierung - beispielsweise das Rotfärben einer Zelle mithilfe von Formeln, die kleiner oder größer als sind. Weitere Informationen zur Verwendung von IF-Anweisungen in Excel.

Eines der weniger häufig verwendeten Tools zur bedingten Formatierung ist die Option " Symbolsätze ", die eine Reihe von Symbolen enthält, mit denen Sie eine Excel-Datenzelle in ein Dashboard-Anzeigesymbol verwandeln können.

Wenn Sie auf Regeln verwalten klicken, werden Sie zum Manager für bedingte Formatierungsregeln weitergeleitet .

Abhängig von den Daten, die Sie vor der Auswahl des Symbolsatzes ausgewählt haben, wird im Manager-Fenster die Zelle mit dem gerade ausgewählten Symbolsatz angezeigt.

Wenn Sie auf Regel bearbeiten klicken, wird das Dialogfeld angezeigt, in dem die Magie ausgeführt wird.

Hier können Sie die logische Formel und Gleichungen erstellen, die das gewünschte Dashboard-Symbol anzeigen.

In diesem Beispiel-Dashboard wird die für verschiedene Aufgaben aufgewendete Zeit im Vergleich zur geplanten Zeit angezeigt. Wenn Sie das halbe Budget überschreiten, wird ein gelbes Licht angezeigt. Wenn Sie das Budget vollständig überschritten haben, 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 ausgegeben.

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 eine weitere.

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 derselben Zeile wie dieses Element zurückgeben können.

Leider setzt die Funktion voraus, dass sich das Element, nach dem Sie in der Liste suchen, in der linken Spalte befindet und die gesuchten Daten auf der rechten Seite sind. Aber was ist, wenn sie vertauscht sind?

Was ist im folgenden Beispiel, wenn ich die Aufgabe, die ich am 25.06.2008 ausgeführt habe, anhand der folgenden Daten finden 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 - entgegen der normalen Funktionsweise von VLookup.

Wenn Sie Microsoft Excel Pro-User-Foren lesen, werden Sie feststellen, dass dies mit VLookup nicht möglich ist und dass Sie dazu eine Kombination aus Index- und Übereinstimmungsfunktionen verwenden müssen. 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 folgendermaßen 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 können, ist die Spalte in der obigen Tabelle tatsächlich 1, oder?

Das stimmt, aber mit der Funktion „CHOOSE“ manipulieren Sie die beiden Felder.

Sie weisen Datenbereichen Referenz- „Index“ -Nummern zu, dh, Sie weisen Index Nummer 1 die Datumsangaben und Index Nummer 2 die Aufgaben zu.

Wenn Sie also „2“ in die VLookup-Funktion eingeben, verweisen Sie in der CHOOSE-Funktion auf Indexnummer 2. Cool, richtig?

Jetzt verwendet der VLookup die Datumsspalte und gibt die Daten aus der Aufgabenspalte zurück, obwohl sich die Aufgabe auf der linken Seite befindet.

Jetzt, da Sie diesen kleinen Leckerbissen kennen, stellen Sie sich vor, was Sie sonst noch tun können!

Wenn Sie versuchen, andere erweiterte Daten-Suchaufgaben auszuführen, lesen Sie unbedingt den vollständigen Artikel von Dann zum Suchen von Daten in Excel mithilfe von Suchfunktionen.

2. Verschachtelte Formel zum Analysieren von Zeichenfolgen

Hier ist noch eine verrückte Excel-Formel für Sie.

Es kann vorkommen, dass Sie Daten aus einer externen Quelle, die aus einer Zeichenfolge begrenzter Daten besteht, in Microsoft Excel importieren.

Sobald Sie die Daten eingegeben haben, möchten Sie sie in die einzelnen Komponenten aufteilen. Im Folgenden finden Sie ein Beispiel für Informationen zu Name, Adresse und Telefonnummer, die durch das Zeichen „;“ getrennt sind.

So können Sie diese Informationen mithilfe einer Excel-Formel analysieren (sehen Sie nach, ob Sie diesem Wahnsinn geistig folgen können):

Um das am weitesten links stehende Element (den Namen der Person) aus dem ersten Feld zu extrahieren, verwenden Sie einfach eine LEFT-Funktion in der Formel.

 "=LEFT(A2, FIND(";", A2, 1)-1)" 

So funktioniert diese Logik:

• Sucht die Textzeichenfolge von A2
• Findet das Trennzeichen ";"
• Subtrahiert eins für die richtige Position des Endes dieses Zeichenfolgenabschnitts
• Ergreift 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?

Möglicherweise gibt es einfachere Möglichkeiten, dies zu tun, aber da wir versuchen möchten, die verrückteste mögliche geschachtelte 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 RECHTS-Funktionen verschachteln, um den Textabschnitt bis zu diesem ersten ";" - Symbol zu erfassen, und die LINKE Funktion erneut ausführen. So sieht es aus, wenn Sie den Hausnummer-Teil der Adresse extrahieren.

 "=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" 

Es sieht verrückt aus, ist aber nicht schwer zusammenzubauen. Ich habe nur diese Funktion übernommen:

 RIGHT(A2, LEN(A2)-FIND(";", A2)) 

Und an jeder Stelle in der LEFT-Funktion darüber, an der es ein „A2“ gibt, einfügen.

Das extrahiert den zweiten Abschnitt der Zeichenfolge korrekt.

Für jeden nachfolgenden Abschnitt der Zeichenfolge muss ein weiteres Nest erstellt werden. Nehmen Sie jetzt einfach die verrückte „RIGHT“ -Gleichung, die Sie für den letzten Abschnitt erstellt haben, und übergeben Sie diese in eine neue RIGHT-Formel, wobei die vorherige RIGHT-Formel an der Stelle in sich eingefügt wird, an der 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 nimmst du DIESE Formel und platzierst sie in die ursprüngliche LINKE Formel, wo immer es ein „A2“ gibt.

Die abschließende Formel 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 Zeichenfolge.

Wiederholen Sie den obigen Vorgang erneut, um den nächsten Abschnitt zu extrahieren.

Ihre Excel-Formeln können sehr durcheinander sein, aber alles, was Sie tun, ist, lange Formeln auszuschneiden und in sich selbst einzufügen, um lange Nester zu erstellen, die tatsächlich funktionieren.

Ja, dies erfüllt die Anforderung 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 aus und wählen Sie dann unter dem Menüpunkt Daten die Option Text in Spalten aus .

Dadurch wird ein Fenster geöffnet, in dem Sie die Zeichenfolge durch ein beliebiges Trennzeichen teilen können.

Mit ein paar Klicks können Sie das Gleiche tun wie mit dieser verrückten Formel oben… aber wo ist der Spaß dabei?

Verrückt werden mit Microsoft Excel-Formeln

Also da hast du es. Die obigen Formeln zeigen, wie übertrieben eine Person sein kann, wenn sie Microsoft Excel-Formeln erstellt, um bestimmte Aufgaben auszuführen.

Manchmal sind diese Excel-Formeln nicht der einfachste (oder beste) Weg, um Dinge zu erreichen. Die meisten Programmierer werden Sie auffordern, es einfach zu halten, und das gilt auch für Excel-Formeln. Sie können sogar integrierte Funktionen wie Power Query verwenden.

Wenn Sie wirklich ernsthaft mit Excel arbeiten möchten, lesen Sie unser Handbuch für Anfänger zur Verwendung von Microsoft Excel. Das Handbuch für Anfänger zu Microsoft Excel Das Handbuch für Anfänger zu Microsoft Excel In diesem Handbuch für Anfänger können Sie sich mit Microsoft Excel vertraut machen. Die hier aufgeführten grundlegenden Tipps für Tabellenkalkulationen helfen Ihnen dabei, Excel selbst zu lernen. Weiterlesen . Es hat alles, was Sie brauchen, um Ihre Produktivität mit Excel zu steigern. Lesen Sie danach unbedingt unser Übersichtsblatt mit den wichtigsten Excel-Funktionen. Das Übersichtsblatt mit den wichtigsten Microsoft Excel-Formeln und -Funktionen. Das Übersichtsblatt mit den wichtigsten Microsoft Excel-Formeln und -Funktionen. Weiterlesen .

Erfahren Sie mehr über: Microsoft Excel, Microsoft Office 2016, Tabellenkalkulation.