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

Microsoft Excel ist eines der leistungsfähigsten Tabellenkalkulationsprogramme mit einer beeindruckenden Sammlung von integrierten Tools und Funktionen. In diesem Artikel erfahren Sie anhand von drei nützlichen Beispielen, wie leistungsfähig Excel-Formeln und bedingte Formatierungen sein können.

In Microsoft Excel graben

Wir haben eine Reihe von Möglichkeiten erläutert, wie Sie Excel besser nutzen können, z. B. zum Erstellen einer eigenen Kalendervorlage oder als Projektverwaltungstool.

Ein Großteil der Macht liegt 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 Kalkulationstabelle einfügen.

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

Bedingte Formatierung mit Formeln

Eines der Tools, die nicht oft genug verwendet werden, ist die bedingte Formatierung. Wenn Sie nach weitergehenden Informationen zur bedingten Formatierung in Microsoft Excel suchen, sollten Sie unbedingt den Artikel von Sandy zur Formatierung von Daten in Microsoft Excel mit der bedingten Formatierung lesen.

Mit der Verwendung von Excel-Formeln, Regeln oder nur wenigen wirklich einfachen Einstellungen können Sie eine Tabelle in ein automatisiertes Dashboard umwandeln.

Um zur bedingten Formatierung zu gelangen, klicken Sie einfach auf Zuhause Klicken Sie auf die Registerkarte Bedingte Formatierung Symbolleistensymbol.

Bei der bedingten Formatierung gibt es viele Optionen. Die meisten davon gehen über den Rahmen dieses Artikels hinaus, aber die Mehrheit von ihnen bezieht sich auf das Hervorheben, Färben oder Schattieren von Zellen basierend auf den Daten in dieser Zelle.

Dies ist wahrscheinlich die häufigste Verwendung von bedingten Formatierungsfunktionen, z. B. das Rotieren einer Zelle mit weniger als oder größer als Formeln. Weitere Informationen zur Verwendung von IF-Anweisungen in Excel.

Eines der weniger benutzten Werkzeuge für die bedingte Formatierung ist das Symbolsätze Diese Option bietet eine Reihe von Symbolen, mit denen Sie eine Excel-Datenzelle in ein Dashboard-Anzeigesymbol verwandeln können.

Wenn Sie auf klicken Regeln verwalten, es bringt dich zum Manager für bedingte Formatierungsregeln.

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

Wenn Sie auf klicken Regel bearbeiten, Sie sehen den Dialog, in dem die Magie stattfindet.

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

In diesem Beispiel-Dashboard wird die für verschiedene Aufgaben aufgewendete Zeit gegenüber der budgetierten Zeit angezeigt. Wenn Sie das halbe Budget überschreiten, wird ein gelbes Licht angezeigt. Wenn Sie das Budget vollständig überschreiten, wird es rot.

Wie Sie sehen, zeigt dieses Dashboard, dass die Zeitbudgetierung nicht erfolgreich ist.

Fast die Hälfte der Zeit wird weit über die budgetierten Beträge ausgegeben.

Zeit, sich neu zu konzentrieren und Ihre Zeit besser zu verwalten!

1. Verwendung 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 das Element zurückgeben können.

Leider erfordert die Funktion, dass sich das Element, nach dem Sie in der Liste suchen, in der linken Spalte befindet und dass die Daten, nach denen Sie suchen, auf der rechten Seite sind. Was passiert, wenn sie gewechselt werden??

Was passiert, wenn ich im folgenden Beispiel die Aufgabe, die ich am 25.06.2014 ausgeführt habe, aus den folgenden Daten finden möchte??

In diesem Fall durchsuchen Sie die Werte auf der rechten Seite, und Sie möchten den entsprechenden Wert auf der linken Seite zurückgeben - gegenüber der normalen Funktionsweise von VLookup.

Wenn Sie in Microsoft Excel-Foren für Benutzer lesen, werden Sie feststellen, 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 veranlassen, auf diese Weise zu arbeiten, indem Sie eine CHOOSE-Funktion darin verschachteln. In diesem Fall würde die Excel-Formel folgendermaßen aussehen:

= VLOOKUP (DATUM (2018,6,25), WÄHLEN (1,2, E2: E8, A2: A8), 2,0)

Diese Funktion bedeutet, dass Sie das Datum 25.06.2013 in der Suchliste suchen und dann den entsprechenden Wert aus dem Spaltenindex zurückgeben möchten.

In diesem Fall werden Sie feststellen, dass der Spaltenindex lautet “2”, aber wie Sie sehen können, ist die Spalte in der obigen Tabelle tatsächlich 1, richtig?

Das stimmt, aber was machen Sie mit dem “WÄHLEN” Funktion manipuliert die beiden Felder.

Sie weisen eine Referenz zu “Index” Zahlen zu Datenbereichen - Zuordnung der Datumsangaben zur Indexnummer 1 und der Aufgaben zur Indexnummer 2.

Also, wenn Sie tippen “2” In der VLookup-Funktion verweisen Sie tatsächlich auf Indexnummer 2 in der CHOOSE-Funktion. Cool, richtig?

Nun verwendet das VLookup das Datum Spalte und gibt die Daten aus der Aufgabe Spalte, obwohl Task auf der linken Seite ist.

Nun, 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 den vollständigen Artikel von Dann über das Finden 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 mit Trennzeichen besteht.

Sobald Sie die Daten eingegeben haben, möchten Sie diese Daten in die einzelnen Komponenten auslesen. Hier ein Beispiel für Informationen zu Name, Adresse und Telefonnummer, die durch das Symbol getrennt werden “;” Charakter.

So können Sie diese Informationen mithilfe einer Excel-Formel analysieren (prüfen Sie, ob Sie diesen Wahnsinn geistig mitverfolgen können):

Für das erste Feld würden Sie zum Extrahieren des ganz linken Elements (Name der Person) einfach eine LEFT-Funktion in der Formel verwenden.

= LINKS (A2, FIND (;; A2,1) -1)

So funktioniert diese Logik:

• Sucht die Textzeichenfolge von A2
• Findet das “;” Trennzeichen
• Subtrahiert eins für die korrekte Position des Endes dieses Zeichenkettenabschnitts
• Ergreift den ganz linken Text bis zu diesem Punkt

In diesem Fall lautet der Text ganz links “Ryan”. Mission erfüllt.

3. Verschachtelte Formel in Excel

Aber was ist mit den anderen Abschnitten??

Es gibt vielleicht einfachere Wege, dies zu tun, aber da wir versuchen, die verrückteste geschachtelte Excel-Formel zu erstellen (die tatsächlich funktioniert), wollen wir einen einzigartigen Ansatz verwenden.

Um die Teile auf der rechten Seite zu extrahieren, müssen Sie mehrere RECHTE Funktionen verschachteln, um den Textabschnitt bis zu diesem Punkt zu erfassen “;” Symbol und führen Sie die LEFT-Funktion erneut aus. So sieht das aus, um den Straßennummernteil der Adresse zu extrahieren.

"LINKS ((RECHTS (A2, LEN (A2) -FIND (;;", A2))), FINDEN (";", (RECHTS (A2, LEN (A2) -FIND (";", A2)) ), 1) -1)

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

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

Und fügte es an jeder Stelle in der LEFT-Funktion oben ein, wo sich eine befindet “A2”.

Dadurch wird der zweite Abschnitt der Zeichenfolge korrekt extrahiert.

Für jeden nachfolgenden Abschnitt der Zeichenfolge muss ein anderes Nest erstellt werden. So, jetzt nimmst du einfach den Verrückten “RECHT” Gleichung, die Sie für den letzten Abschnitt erstellt hatten, und übergeben Sie diese in eine neue RECHTE Formel, wobei die vorherige RECHTE Formel an jeder beliebigen Stelle in sich selbst eingefügt wurde “A2”. So sieht das aus.

(RECHTS ((RECHTS (A2, LEN (A2) -FIND (";", A2))), LEN ((RECHTS (A2, LEN (A2) -FIND (";", A2)))) - FINDEN ( ";", (RECHTS (A2, LEN (A2) -FIND (";", A2))))))

Dann nimmst du DIESE Formel und platzierst sie in der ursprünglichen LEFT-Formel, wo auch immer eine “A2”.

Die abschließende Mind-Bending-Formel sieht folgendermaßen aus:

"= LINKS ((RECHTS ((A2, LEN (A2) - FIND (;; A2))), LEN ((RECHTS (A2, LEN (A2) - FIND ((A2)))) ) -FIND (";", (RECHTS (A2, LEN (A2) -FIND (";", A2)))))), FINDEN (";", (RECHTS ((RECHTS (A2, LEN (A2)) -FIND (;; A2)), LEN ((RECHTS (A2, LEN (A2) -FIND (;; A2,))) - FIND (;;;, (RECHTS (A2, LEN (A2 ) -FIND (;; A2))))), 1) -1)

Diese Formel extrahiert richtig “Portland, ME 04076” aus dem ursprünglichen String.

Um den nächsten Abschnitt zu extrahieren, wiederholen Sie den obigen Vorgang noch einmal.

Ihre Excel-Formeln können sehr unregelmäßig werden, aber Sie müssen nur lange Formeln in sich schneiden und einfügen, so dass lange Nester entstehen, die tatsächlich funktionieren.

Ja, das erfüllt die Anforderung für “verrückt”. Aber seien wir ehrlich, es gibt einen viel einfacheren Weg, das Gleiche mit einer Funktion zu erreichen.

Wählen Sie einfach die Spalte mit den begrenzten Daten und dann unter Daten Menüpunkt auswählen Text in Spalten.

Dadurch wird ein Fenster geöffnet, in dem Sie die Zeichenfolge nach einem beliebigen Trennzeichen aufteilen können.

Mit ein paar Klicks können Sie dasselbe wie diese verrückte Formel oben machen… aber wo ist der Spaß dabei??

Verrückt werden mit Microsoft Excel

Also da hast du es. Die obigen Formeln zeigen, wie übertrieben eine Person beim Erstellen von Microsoft Excel-Formeln zur Ausführung bestimmter Aufgaben sein kann.

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.

Wenn Sie sich mit der Verwendung von Excel wirklich auskennen möchten, sollten Sie unser Anfängerhandbuch zur Verwendung von Microsoft Excel durchlesen. Das Anfängerhandbuch für Microsoft Excel Das Anfängerhandbuch für Microsoft Excel Verwenden Sie dieses Anfängerhandbuch, um Ihre Erfahrungen mit Microsoft Excel zu beginnen. Die grundlegenden Tipps zur Tabellenkalkulation helfen Ihnen dabei, Excel selbstständig zu lernen. Weiterlesen . Es bietet alles, was Sie benötigen, um Ihre Produktivität mit Excel zu steigern.

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